Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am struggling with a scenerio. I want a DAX solution rather than "merge and append".
I have a FACT table and a related Dimension Table. I want to count values in a column in FACT table that match with values in a column in DIMENSION. Any help is appreaciated, Thanks
Solved! Go to Solution.
FACT:
EqNo,Category
1001,M
1002,T
1005,T
DIM:
EqNo,Class
1001,A
1002,A
1003,B
1004,D
1005,E
Expected result is 3 as 1001,1002, and 1005 Category (FACT) do match with Class (Dimension).
Try something like this:
Measure =
VAR _tmpTable = ADDCOLUMNS(Fact, "InDIM",
VAR _curEqNo = [EqNo]
RETURN
COUNTROWS(FILTER(Dim, Dim[EqNo] = _curEqNo)))
RETURN
COUNTROWS(FILTER(_tmpTable, [InDIM] > 0))
This hasn't been tested and typed without intellisense so forgive any typos 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Proud to be a Super User!
Can you give an example of some data and expected outcome? What do you envision the outcome to look like? Is it just a scalar value? Or is it evaluated against every row in your fact table?
Proud to be a Super User!
FACT:
EqNo,Category
1001,M
1002,T
1005,T
DIM:
EqNo,Class
1001,A
1002,A
1003,B
1004,D
1005,E
Expected result is 3 as 1001,1002, and 1005 Category (FACT) do match with Class (Dimension).
Try something like this:
Measure =
VAR _tmpTable = ADDCOLUMNS(Fact, "InDIM",
VAR _curEqNo = [EqNo]
RETURN
COUNTROWS(FILTER(Dim, Dim[EqNo] = _curEqNo)))
RETURN
COUNTROWS(FILTER(_tmpTable, [InDIM] > 0))
This hasn't been tested and typed without intellisense so forgive any typos 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Smartly done! worked like a charm. Thank you so much.
I tried to modify it to target another need. How do I count Non Matching values in each table?
1 - Dim has 2 values (1003 and 1004) that don't exist in Fact?
2 - (assuming) FACT has 1009 that does not exist in Dim?
Thanks in advance!!
Proud to be a Super User!
bigno!! Life savior!
User | Count |
---|---|
113 | |
106 | |
83 | |
67 | |
42 |
User | Count |
---|---|
157 | |
110 | |
84 | |
78 | |
60 |