A relationship is not working when using a measure

Question

My model contains a fact table and a dimension. The dimension contains Full Name, Login, Access, DOMAIN. Domain is a numeric number which is used as a key to the fact table. Here is some dummy data:

FullName	Login	ACCESS	Domain
John Doe	NET\JDOE	USER	3000454
John Doe	NET\JDOE	USER	3002656
John Doe	NET\JDOE	USER	3000454
Lara Doe	NET\LDOE	USER	3000454
Lara Doe	NET\LDOE	USER	3000001

The dimension does not have a field with unique values, and as you can see users can have the same domain. I tried two approaches that partially worked. I directly created many-to-many relationship. It works fast and correct when used in a table visual. When I transfer the logic in a measure and add the measure to the same visual I get results as if there is something wrong with the relationship. Another approach I tried is duplicate the dimension, remove all columns except the key column (Domain) and remove the duplicates. That way I created bridge relationship between the fact and the dimension (with 1:* cardinality) and I set both relationships to filter both ways.

Yet, the result is:

This visual has FullName coming from the dimension, SUM of a field from the fact, and a measure. On the visual level I have a filter on the FullName column to exclude a person, and a Year field from the fact to include only 2024. The measure is basically Calculate the sum of the fact field, and the filters applied to the visual.

I have tried the bridge technique many times before, and it has worked. But in this current situation, I do not see what the issue could be. How could simply dragging the field and using visual-level filters work, and using a measure not work? Maybe I do not fully understand the limitations of many-to-many relationship and both-filter direction?

I could provide sample from the fact table or a pbix, if there is not an obvious solution.

Can you please provide some sample data from the fact table and the code for the measure? It appears that the measure isn't evaluating for each row of the table. — Nick A
– Nick A, Commented Sep 16, 2024 at 13:26
drive.google.com/file/d/1fSbbMvX8DeBnnFCdjY9tpZM8loc8QN8d/… Here is a link. I was able to recreate the issue. I have created bridge table and all the relationships. You just need to switch active/inactive if you want to test. :) — ttruhcheva
– ttruhcheva, Commented Sep 16, 2024 at 13:49
I made a quick look , your issue is mainly with the modeling and the many to many relationship. I will come up with something tomorrow stay tuned — user9517769
– user9517769, Commented Sep 16, 2024 at 22:02

Nick A · Accepted Answer · 2024-09-17 14:47:26Z

1

I removed the Dimension part of your Result Measure and it works perfectly fine:

Result = 
CALCULATE(SUM('Fact'[Result])
    , 'Fact'[Year] = 2024
    )

Example Output:

edited Sep 17, 2024 at 14:47

answered Sep 17, 2024 at 14:41

Nick A

1,1202 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ttruhcheva Over a year ago

First chance I get I will test this on true data. The measures will be more complex, but I will apply your approach. Thank you for taking the time!

ttruhcheva Over a year ago

That actually worked. My report will have RLS filtered by the username, so each person will see their personal data. In that case I do not need filter on Full Name other than the RLS filter.

Collectives™ on Stack Overflow

A relationship is not working when using a measure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related