Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |