Forum Discussion
Multiple TRUE values on a SWITCH TRUE(); Function
Hello PBI Community!
I have a SWITCH function that evaluates with the value in one particular column of my table is true (name of an employee) to call a specific Measure as Value for a visual. This works great and all, but when no name is selected, I'd like call a different measure, but the function doesn't work:
SWITCH( TRUE();
VALUES('All'[COLAB]) = "Employee 1"; [# Total Hours Worked by Employee 1];
VALUES('All'[COLAB]) = "Employee 2"; [# Total Hours Worked by Employee 2];
VALUES('All'[COLAB]) = "Employee 3"; [# Total Hours Worked by Employee 3];
VALUES('All'[COLAB]) = "Employee 4"; [# Total Hours Worked by Employee 4];
VALUES('All'[COLAB]) = "Employee 5"; [# Total Hours Worked by Employee 5];
VALUES('All'[COLAB]) = "Employee 6"; [# Total Hours Worked by Employee 6];
VALUES('All'[COLAB]) = "Employee 7"; [# Total Hours Worked by Employee 7];
[_Sum Spend Time by All]
)
This works with a table that has all these names and every time I select one name on the table, the line graph that containes the aboe function work nicely, but wehn no name is selected it won't call the "[_Sum Spend Time Total (Decimal)]" measure you can see in the last line. I believe this is because the function sees all the values as being true. Is there any way to make this work?
Thanks in advance to this great Community!
Best and stay Safe
Kawan Goncalves
Anonymous
Try this measure
Measure = VAR _colab = VALUES ( 'All'[COLAB] ) VAR result = SWITCH ( TRUE (); "Employee 1" IN _colab; [# Total Hours Worked by Employee 1]; "Employee 2" IN _colab; [# Total Hours Worked by Employee 2]; "Employee 3" IN _colab; [# Total Hours Worked by Employee 3]; "Employee 4" IN _colab; [# Total Hours Worked by Employee 4]; "Employee 5" IN _colab; [# Total Hours Worked by Employee 5]; "Employee 6" IN _colab; [# Total Hours Worked by Employee 6]; "Employee 7" IN _colab; [# Total Hours Worked by Employee 7]; [_Sum Spend Time by All] ) RETURN result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
2 Replies
- nandukrishnavs
Community Champion
Anonymous
Try this measure
Measure = VAR _colab = VALUES ( 'All'[COLAB] ) VAR result = SWITCH ( TRUE (); "Employee 1" IN _colab; [# Total Hours Worked by Employee 1]; "Employee 2" IN _colab; [# Total Hours Worked by Employee 2]; "Employee 3" IN _colab; [# Total Hours Worked by Employee 3]; "Employee 4" IN _colab; [# Total Hours Worked by Employee 4]; "Employee 5" IN _colab; [# Total Hours Worked by Employee 5]; "Employee 6" IN _colab; [# Total Hours Worked by Employee 6]; "Employee 7" IN _colab; [# Total Hours Worked by Employee 7]; [_Sum Spend Time by All] ) RETURN result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂 - AnonymousNot applicableHi there.
Sorry to say that but the measure is not how you should code in DAX. If you happen to have 100 or 1000 employees, are you going to add or remove manually cases to/from the measure every time the number of employees changes?
Just think about how cumbersome the solution is right now.
My suggestion is that you should first learn how to properly model data for Power BI and then write simple DAX against a good model. The current code, and I suspect the model as well, is just hardly maintainable. You can start with these:
https://www.youtube.com/watch?v=78d6mwR8GtA
https://www.youtube.com/watch?v=_quTwyvDfG0
Best
D