Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

finding matching values between a dimension and fact table

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

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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). 

View solution in original post

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

If you want that to be one number (non matches in FACT + non matches in DIM), you can alter my measure like so:
Measure =
VAR _tmpTableF = ADDCOLUMNS(Fact, "InDIM",
VAR _curEqNo = [EqNo]
RETURN
COUNTROWS(FILTER(Dim, Dim[EqNo] = _curEqNo)))
VAR _tmpTableD = ADDCOLUMNS(Dim, "InFACT",
VAR _curEqNo = [EqNo]
RETURN
COUNTROWS(FILTER(Fact, Fact[EqNo] = _curEqNo)))
RETURN
COUNTROWS(FILTER(_tmpTableF, [InDIM] = 0)) + COUNTROWS(FILTER(_tmpTableD, [InFACT] = 0))

This was typed on phone so please forgive any typos:)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
JarroVGIT
Resident Rockstar
Resident Rockstar

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

If you want that to be one number (non matches in FACT + non matches in DIM), you can alter my measure like so:
Measure =
VAR _tmpTableF = ADDCOLUMNS(Fact, "InDIM",
VAR _curEqNo = [EqNo]
RETURN
COUNTROWS(FILTER(Dim, Dim[EqNo] = _curEqNo)))
VAR _tmpTableD = ADDCOLUMNS(Dim, "InFACT",
VAR _curEqNo = [EqNo]
RETURN
COUNTROWS(FILTER(Fact, Fact[EqNo] = _curEqNo)))
RETURN
COUNTROWS(FILTER(_tmpTableF, [InDIM] = 0)) + COUNTROWS(FILTER(_tmpTableD, [InFACT] = 0))

This was typed on phone so please forgive any typos:)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

bigno!! Life savior!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.