DAX Formula Behaves Like There is No relationship between same table fields

[Anonymous]

Hello all,

I have 1 fact table (HR Metrics) with the following fields: Measure Type, Measure - Final Count, Measure - Preview Count, Date of Export, Business Group (+ other dimensions). I have a few dimensions too, but I do not think they are relevant to my issue.

The problem is that when I try to calculate the sum of Measure - Preview Count or Measure - Final Count filtered by Measure Type field, they act like there is no relationship, but they are in the same table.

For Example, I created a measure: Total Headcount = CALCULATE ( SUM ( Measure - Final Count), `HR Metrics`[Measure Type] = "Headcount" )

As you can see from the attached screenshot, the results behaves like there is no relationship between my Total Headcount measure and the "Measure Type", even if "Total Headcount" is built based on "Measure - Preview Count" which is in the same table as "Measure Type".

If I do a SUM summarization of "Measure - Preview Count", I get the right results for each "Measure Type". I colored with RED the wrong results.

Please can you assist?

Thank you,

Diana

[Anonymous]

You need a bit of theory about how CALCULATE works. Otherwise, you'll never understand what the code you write does. In your case, in the measure you are OVERWRITING any filters that are put by the visual on the Measure Type column and this is why you see the number replicated across the table. I don't know what your measure should do, to be honest. Total Headcount gives you the right figure for all the measure types. If you want to only show the figure when Headcount has been selected and blank it out for other types, then you have to wrap the condition in CALCULATE in KEEPFILTERS.

[Jihwan_Kim · Accepted solution]

Hi,

please try to create modifier with writing full FILTER function.

I cannot see your screen capture properly, so I could not write the accurate measure for your data model.

Please share your sample pbix file, then I can try to create accurate measure.

thanks.