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?
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