How to create a measure that will return 1 if everything is True and 0 if one is False

[lets_bi_88]

Hi Everyone,

I've been trying to create a measure but hasn't been able to get the desired Output.

Please see sample data sets: ID, Status, Create Date and True/False Column

I want to validate if the Status of a particular ID had "Does Not Meet Minimum Qualifications" or FALSE then the count will be zero and 1 if everything is True. The results will be a distinct count and sum of the IDs without "Does Not Meet" or FALSE.

Thank you 🙂 

[talespin · Accepted solution]

hi @lets_bi_88 ,

I am newbie practising DAX, would suggest you to test it thoroughly.

-It is very simple to get the result in table with below measure but total was a problem.

CALCULATE(  

            MINX( TestTable2, IF(TestTable2[Column1], 1, 0)),

            REMOVEFILTERS(TestTable2),

            VALUES(TestTable2[ID])

)

This is how I approached this problem.

At column ID level, first number each row 1 or 0 and take MINX, then group the value by column [ID] and then SUM it, So at column [ID] level sum will always be 1 but at grand total level sum would be 3(Only for ID which have all values as TRUE).

Measure = 

CALCULATE(  

            SUMX(

                    SUMMARIZE(

                        ADDCOLUMNS(                            

                            TestTable2,                            

                            "NewCol",

                            VAR _valID = TestTable2[ID]

                            RETURN MINX( FILTER(TestTable2, TestTable2[ID] = _valID), IF(TestTable2[Column1], 1, 0))

                        ),

                        TestTable2[ID],

                        [NewCol]

                    ),

                    [NewCol]

            ),

            REMOVEFILTERS(TestTable2),

            VALUES(TestTable2[ID])

)

[lbendlin]

use PRODUCTX - it will only return 1 if all parts are 1 (True)

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...