DAX Help: Number of Orders as % of Total w/ Multiple Rows per Order

[andrej_prolic]

Hello everyone,

I need help solving a problem. I have a data set with three columns (Order ID, Type and Compliant). I would like to have a visual that shows the % of total orders that are compliant, regardless of the Type. In this case it would be 2 orders out of 5 (Order 1 and Order 5). However, I'm running into a problem because there are multiple rows for each order, e.g. Order 1 has three rows, and the Compliant column has "Yes" twice, however, I want to count the order only once to get to the right output of 40% (2 order out of 5 are compliant).

How can I make this work via DAX? Appreciate the response - thank you!

Order IDTypeCompliant

1	A	Yes
1	B	No
1	C	Yes
2	A	No
2	B	No
3	A	No
4	A	No
5	A	No
5	B	Yes

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like but please check the below picture and the attached pbix file.

Expected result measure: =
VAR _allorders =
    COUNTROWS ( DISTINCT ( Data[Order ID] ) )
VAR _yes =
    SUMX (
        ADDCOLUMNS (
            VALUES ( Data[Order ID] ),
            "@Yes",
                CALCULATE (
                    INT (
                        "Yes"
                            IN CALCULATETABLE (
                                VALUES ( Data[Compliant] ),
                                ALL ( Data[Type], Data[Compliant] )
                            )
                    )
                )
        ),
        [@Yes]
    )
RETURN
    DIVIDE ( _yes, _allorders )

[andrej_prolic]

You're the best Jihwan - your formula works perfectly. I will spend time analyzing it. Thank you!