Forum Discussion

Barkop_1's avatar
Barkop_1
Frequent Visitor
3 years ago
Solved

Distinctcount based on conditions in multiple columns

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) )
 
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

2 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    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