Distinctcount based on conditions in multiple columns

[Barkop_1]

Hello, I need dax measure that calculates distinct count of ID column , based on condition form three different columns A,B and C.

Dictinctcount should be calculated when at least of column (A, B, C) has value = 1.

Additionally user can deselect for example column B from calculation, so that then dax should be calculted only on column A, C.

Intuitively i would assume it should be something like below, but I am still trying to figure out row/column contex and general DAX rules: 

DistCount = calculate(distinctcount(RawData[ID]) , AND([IsA],RawData[A]=1) || AND([IsB],RawData[B]=1) || AND([IsC],RawData[C]=1) )

[v-yueyunzh-msft · Accepted solution]

Hi , @Barkop_1 

According to your description, you want to "calculates distinct count of ID column , based on condition form three different columns A,B and C.".

Here are the steps you can refer to :
(1)We need to unpivot your table in Power Query Editor :
We need to select the three columns "A,B,C" and click "Unpivot columns":

Then we can change the column name like this:

Then we can apply the data to desktop .

(2)Then we can create a measure like this:

Measure = CALCULATE( DISTINCTCOUNT('Table'[ID]) , 'Table'[Value] =1)

Then we can get your end result as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[amitchandak]

@Barkop_1 , a better idea would to unpivot the column and then use a distinct count on it

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g