Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity 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
    🙂

  • Anonymous's avatar
    Anonymous
    Not applicable
    Hi 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