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.
I removed the Dimension part of your Result
Measure and it works perfectly fine:
Result =
CALCULATE(SUM('Fact'[Result])
, 'Fact'[Year] = 2024
)
Example Output: