powerbidax

Count specific values across multiple columns based on data in another column

I have 2 table: Data and RC tables Data table contains the orders with 3 codes

Order RC1 RC2 RC3
1000 100 164 100
1001 134 100 null
1002 164 null 134

RC table containing the code descriptions:

RC Description
100 Apple
134 Banana
164 Coconut

I would like to calculate how many times each RC appears in the Data table. In Excel it would be easy with a countif function but in DAX I'm stuck as it is pretty new for me.

Expected output:

RC Description Count
100 Apple 3
134 Banana 2
164 Coconut 2

I tried COUNTROWS & FILTER but I can count data only in the column where the relation is active between the two table (RC - RC1 OR RC2 OR RC3) so I think something is not okay with the relations (???) Can you please help me to find a solution for this problem?

Solution

  • It is much easier and simpler to deal with rows rather than columns with Power BI/DAX. I would recommend modifying your table in Power Query to like:

    Order Item RC Id
    1000 RC1 100
    1000 RC2 164
    1000 RC3 100
    1001 RC1 134
    1001 RC2 100
    1002 RC1 164
    1002 RC3 134

    In Power Query, select your Order column and do a Unpivot Other Columns to get to this. From here, you then only need the one relationship.

    Alternatively, if you keep to your original table structure, you need to create three relationships, one will be active and the other two will be inactive. Then you would use the USERELATIONSHIP function to activate each relationship in turn. Similar to:

    RC Count = 
  var c1 = CALCULATE(COUNTROWS('DataTable'), USERELATIONSHIP('RC'[RC]', DataTable'[RC1]) )
  var c2 = CALCULATE(COUNTROWS('DataTable'), USERELATIONSHIP('RC'[RC]', DataTable'[RC2]) )
  var c3 = CALCULATE(COUNTROWS('DataTable'), USERELATIONSHIP('RC'[RC]', DataTable'[RC3]) )

  return c1 + c2 + c3