Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have the following DAX measure that I heavily simplified down to the issue.
Expense =
VAR _Amount =
CALCULATE(
SUM ( Financial[Amount4] ) ,
ProjectMasterFile[Project_Type] >= 50
)
Return _Amount
If I create a table with columns:
ProjectMasterFile[Project], ProjectMasterFile[Project_Type], Expense
Then the Expense Measure comes out correct.
If I change the table to
ProjectMasterFile[Project_Type], Expense
Then the expense measure repeats the same value for all rows.
The original DAX has several other filters and they all worked and did not have repeated values if I did not have this line
ProjectMasterFile[Project_Type] >= 50
The relations in the data model look correct. So what might I be doing wrong?
Any and all ideas are appreciated.
Thanks
Alan
Solved! Go to Solution.
Filter arguments within CALCULATE normally overwrite existing filters.
A consequence of this is that if one of the filter arguments overwrite filters on columns that are included within your visual, you will see the same value returned for each value of that column within the visual.
You can modify the behaviour of filter arguments so that they instead intersect with existing filters using KEEPFILTERS - possibly this will give you the value you expect.
Try rewriting your measure as:
Expense = VAR _Amount = CALCULATE( SUM ( Financial[Amount4] ) , KEEPFILTERS ( ProjectMasterFile[Project_Type] >= 50 ) ) Return _Amount
Does this give the expected result? Please post backif needed.
Regards,
Owen
Filter arguments within CALCULATE normally overwrite existing filters.
A consequence of this is that if one of the filter arguments overwrite filters on columns that are included within your visual, you will see the same value returned for each value of that column within the visual.
You can modify the behaviour of filter arguments so that they instead intersect with existing filters using KEEPFILTERS - possibly this will give you the value you expect.
Try rewriting your measure as:
Expense = VAR _Amount = CALCULATE( SUM ( Financial[Amount4] ) , KEEPFILTERS ( ProjectMasterFile[Project_Type] >= 50 ) ) Return _Amount
Does this give the expected result? Please post backif needed.
Regards,
Owen
Owen,
Thanks for your response. You are right .... i have used KEEPFILTERS() before, but only when I had something in the filters pane. I had never thought of Fitler Context for a visual (need to rewatch the related videos.
Thanks!!
User | Count |
---|---|
80 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
98 | |
89 | |
82 | |
61 |