Include LOD in Power BI

[asdf1608]

I'm struggling to convert the Include LOD in Power BI. I'm not getting the result required.

Tableau Query is

{ INCLUDE [Number (Sheet11)],[Name (Sheet11)],[Organization (Sheet11)],[Distribution (Sheet11)],[Division (Sheet11)]:
COUNTD(IF [Status (Sheet11)] = "Pass"
THEN [Number of Records]
END)}

Number of Records  = 1

The result coming in Tableau is

The query I kept in power BI

Calcularion 1 =

CALCULATE(

    COUNTAX(

        VALUES(Sheet1[Number ]),

        IF (

           MAX( Sheet1[Status]) = "Pass",

           SUM(Sheet1[Number of Records])

        )

    ),

    ALLEXCEPT('Sheet1',Sheet1[Number ], Sheet1[ Name], Sheet1[Organization] ,'Sheet1'[Distribution], Sheet1[Division])

)

the result in power BI is 

I am not sure where I'm wrong. But the result in tableau and Power BI are totally different

How should we convert Include LOD in Power BI DAX

I have attached my Tableau and Power BI Fie as reference.

Book1 PBI.pbix

Book1.xlsx

Book1.twbx

Help in this is really needed.

Thanks in advance.

[v-jialongy-msft · Accepted solution]

Hi @asdf1608 

Try the following DAX

Calculation2 = 
CALCULATE(
    DISTINCTCOUNT('Sheet1'[Number ]),
    FILTER(
        ALLEXCEPT(
            'Sheet1',
            'Sheet1'[Number ],
            'Sheet1'[ Name],
            'Sheet1'[Organization],
            'Sheet1'[Distribution],
            'Sheet1'[Division]
        ),
        'Sheet1'[Status] = "Pass"
    )
)

This is the result you want 

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.