I was looking for a formule to reset a column in my tabular model to zero based on the user who is logged in. This is quick workaround. In SSAS Tabular 2016, it’s impossible to hide columns. We are used to a dimensional model. We had to find a quick fix.
After trial and error, I found a formula. The trick is to check the user who is using the Tabular model. When the user is in my hard-coded list (check USERNAME() ), we do a * 1, otherwise we do a * 0.
DAX Formula:
Marge Reeel Totaal:=
SUMX(
DeclaratieRegel;
DeclaratieRegel[_Marge Reeel Totaal] *
SWITCH(
USERNAME();
“NL\00007892”; 1;
“NL\00007444”; 1;
0
)
)
Frequently asked questions
How do I set a column to zero for certain users in a DAX tabular model?
Wrap the column in SUMX and multiply it with a SWITCH on USERNAME(). Users in your hard-coded list get a 1. Everyone else gets a 0, so the column shows zero for them.
Can you hide a column in SSAS Tabular 2016?
No. In SSAS Tabular 2016 it is impossible to hide columns. A DAX workaround sets the column to zero for users who may not see it.
How does the USERNAME() function work in a DAX measure?
USERNAME() returns the user who is using the tabular model. The measure checks it against a hard-coded list of names such as NL\00007892. A match multiplies the value by 1.
What does the SWITCH function do in this DAX formula?
SWITCH compares USERNAME() with each listed user. A listed user returns 1. The last argument, 0, applies to everyone else, so their column value is multiplied by zero.
Why use SUMX for this security workaround in DAX?
SUMX runs row by row over the DeclaratieRegel table. It multiplies each row of the column Marge Reeel Totaal by 1 or 0, depending on the user. The sum is then zero for users outside the list.
Comments
Related blogs
Enrich customer data with the postal code table
The 5 best examples Of Big Data in real life
Top 3 of the Best ETL tools in the Market in 2017