powerbimodeldaxpowerbi-desktopdata-modeling

A relationship is not working when using a measure


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:

enter image description here

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.


Solution

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

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

    Example Output:

    Example Output