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'm getting strange behaviour when I apply a 'not blank' filter to a FACT(ish) table, which is being filtered by slicers, one on a dimension table and one on a value in the FACT table. This example is for demonstration only, I know I need to use dimension tables; however, this is potentially highlighting an issue with Power BI itself.
I have this FACT type table:
With the DIM Area.ID in a one-to-many with the above table:
I want to display a table that presents all of the main table data plus a column which returns the sum of the score for the business. This column is defined by the measure:
Business Score =
CALCULATE(
SUM(QuestionScores[Score]),
'DIM Area'[Area] = "Business",
QuestionScores[Sub-Area]="Business"
)
Now I add this to my table and apply filters on the Area and Sub-Area (this step is necessary):
Now in the filters pane, if I apply a filter on Sum of Score is not blank, nothing changes:
However, if I drag the Score column to the Filters pane for the visual (which we would think is identical to the above) and apply the same 'is not blank' filter. The measure breaks and doesn't display any information.
This persists, even if I delete the original filter:
And removing the filter lets the measure work again:
Interestingly. If I push the Sub-Area to its own dimension and rework the Business Score measure to filter by that, everything works no matter what visual filters are appled. Similarly, if the slicers on the left have no selected values, the measure works.
As such, this issue seems to be a combination of:
-Filtering by the main (fact) and dimension in the same measure, overwriting filters applied by slicers
-Adding a visual level filter on the Score column not being blank, by dragging the field in from the Fields pane
I know there are flaws with the model of this. It isn't a proper Star Schema and the fact is operating at multiple grains; however, this shouldn't affect how Power BI processes this sort of filter.
I assume the issue lies in how Power BI applied filters as I cannot see any logical reason why this doesn't work.
@tbennett93 , Try a measure like this and check
Business Score =
CALCULATE(
SUM(QuestionScores[Score]),
Filter('DIM Area', 'DIM Area'[Area] = "Business"),
Filter(QuestionScores, QuestionScores[Sub-Area]="Business")
)
This does not work, with or without the filters, as the first argument in FILTER doesn't clear the filters on the table and so the filter context clashes with 'Business' for all rows other than 'Business' in the table.
I have assumed you meant to say:
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |