Forum Discussion

SarathB2's avatar
SarathB2
Frequent Visitor
4 years ago
Solved

Need help on DAX

Hello Experts, I have a table and having one calculated column with Pass/Fail. I want output like if all pass for one category then consider as pass and even one fail occur for that category th...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

     Check that the Material has no false value in its group.

     

    Output =
NOT (
    FALSE
        IN CALCULATETABLE (
            VALUES ( Materials[Check] ),
            ALLEXCEPT ( Materials, Materials[Material] )
        )
)

     

    Slightly less intuitive but simpler:

    Output =
CALCULATE (
    SELECTEDVALUE ( Materials[Check] ),
    ALLEXCEPT ( Materials, Materials[Material] )
)
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Yes, the second one will return blanks when multiple values are present. These get coerced into FALSE values if the column type is a logical boolean.

     

    To work with "Yes" / "No", I think you could just add an argument to SELECTVALUE for what to return instead of blank when there are multiple values. That is, SELECTEDVALUE ( Materials[Check], "No" ).

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

 Check that the Material has no false value in its group.

 

Output =
NOT (
    FALSE
        IN CALCULATETABLE (
            VALUES ( Materials[Check] ),
            ALLEXCEPT ( Materials, Materials[Material] )
        )
)

 

Slightly less intuitive but simpler:

Output =
CALCULATE (
    SELECTEDVALUE ( Materials[Check] ),
    ALLEXCEPT ( Materials, Materials[Material] )
)
  • SarathB2's avatar
    SarathB2
    Frequent Visitor
    4 years ago

    Thanks a lot AlexisOlson . Great solution.It's working, I will check it in my real scenorio.
    Greatful for quick response.

  • SarathB2's avatar
    SarathB2
    Frequent Visitor
    4 years ago

    Hi AlexisOlson ,
    In case of something like "YES" or "NO" instead of "True" or "False" , first solution is working and Second solution is not giving expected result... Right?

     

    Thanks & Regards,

    Sarath.

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      Yes, the second one will return blanks when multiple values are present. These get coerced into FALSE values if the column type is a logical boolean.

       

      To work with "Yes" / "No", I think you could just add an argument to SELECTVALUE for what to return instead of blank when there are multiple values. That is, SELECTEDVALUE ( Materials[Check], "No" ).

      • SarathB2's avatar
        SarathB2
        Frequent Visitor
        4 years ago

        Okay , Super thanks for your response AlexisOlson .