Forum Discussion

katto16's avatar
katto16
Icon for Helper I rankHelper I
3 years ago

Count if values from three different columns are unique

Hi. I'm pretty new to Power BI and recently learned how to do Distinct Counts, Calculate, basic DAX etc. I have this thing I'm trying to solve which I have done in Excel but would like to compile all data in Power BI so I'm trying to do this in Power BI. 

 

So this is how the simple version of the data looks like.

For the ease of viewing, I highlighted the duplicated cells. So only if the Unit ID & Actuator ID & Module ID & PWA ID are all unique values, i.e they are not duplicated values, it will be counted as 1. And eventually, I'd like to know the total count of them.

In this case, there is only one which fulfill all the conditions which is DS11115. What would be the easiest and fastest way to do this in Power BI in terms of a measure or a column or any way possible?

 

This is the Excel formula I created. 

where All_Data_2 is the source table, [Date] column as I was matching it according to the date. A2 is the cell that has the date value I'm matching with. 

 

Thanks in advance! Excited to learn more Power BI.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    katto16 , A new column

    =

    var _1 = countx(filter(Table, [Unit ID] = earlier([Unit ID]) ), [Unit ID)

    var _2 = countx(filter(Table, [Unit ID] = earlier([Actuato rID]) ), [Actuator ID)

    var _3 = countx(filter(Table, [Unit ID] = earlier([Module ID]) ), [Module ID)

    var _4 = countx(filter(Table, [Unit ID] = earlier([PWA ID]) ), [PWA ID)

    return

    if(_1+_2+_3+_4+0 >0, 0, 1)

    • katto16's avatar
      katto16
      Icon for Helper I rankHelper I
      3 years ago

      Hi amitchandak 

      Thank you very much for your help. I can't seem to create a variable after the first one. 

      This is the error code.