Forum Discussion
Calculation Groups, Relationships and Filters
Thanks for that Aklys - that PBIX clarified the issue 🙂
The immediate issue is that the calculation items are converting blank values into nonblank values in some cases.
Here is one way to rewrite two the calculation items to avoid this issue:
------------------------------------------------- -- Calculation Item "KPIs__Direct_and_Simplified" ------------------------------------------------- VAR KPIDisplay = [KPI_Display] RETURN IF ( NOT ISBLANK ( KPIDisplay ), SWITCH ( KPIDisplay, 9000, "High Risk", 9001, "At Risk", 9002, "On Track", "Unknown" ) ) ------------------------------------------------- -- CALCULATIONITEM "KPIs__Display" ------------------------------------------------- IF ( CONTAINSSTRING ( SELECTEDMEASURENAME ( ), "*KPI*Display*" ), VAR MeasureValue = SELECTEDMEASURE ( ) RETURN IF ( NOT ISBLANK ( MeasureValue ), SWITCH ( MeasureValue, 9000, "High Risk", 9001, "At Risk", 9002, "On Track", "Unknown" ) ), SELECTEDMEASURE ( ) )The reason you were seeing different behaviour when filtering on Item Type vs Item Type Lookup is more complicated, and relates to auto-exist, compounded by the main issue of converting blanks to nonblanks.
In this example, when the Item Type column on the slicer is from a different table from the other columns used in the visual, non-existent combinations of those columns are not automatically eliminated in the DAX query generated by the visual, so the nonblank measure values (that were originally blank) appear in the visual.
In summary, I would recommend adding checks to ensure that blank values returned by an underlying measure are not converted to nonblank values (within calculation items or otherwise) unless there is a specific reason to do so.
(Also follow the recommendations in the auto-exist article where possible.)
Regards
Hi Aklys
Would you be able to share more detail on your model, the definition of the measure(s) you are displaying along with the calculation item, and the way the visual appears with and without the calculation item applied.
Based on your description, an initial hunch is that the calculation item is not handling blank values of SELECTEDMEASURE () as intended (which would have been automatically hidden when the calculation was not applied).
There shouldn't be any inherent conflict between calculation items and relationships. You should be able to replicate the effect of applying the calculation item by creating a measure with the same code as the calculation item and replacing any occurrences of SELECTEDMEASURE () with a reference to the measure you were using in the visual.
I can't give you the exact data model or functions. But I have a mockup that demonstrates the behaviour I am seeing. Which can be found here https://we.tl/t-2b4T1u63t9
When you apply the filter that isn't a lookup but from a the related table (item type table) that is shown in the slicers to the table, you can see they work fine. You can also then use the calculation items on their own right without the filters and see they work fine. But when you apply both it no longer applies the filter and the calculation item doesn't give the expected result for the items that the filter should be removing that it now displays.
But if you use the filter for the item in the table itself (Item Type Lookup) then it applies both the filter and the calculation item without any issues.
I have also created for comparison looks a table that has no null values and also create a calculation item that is to the direct field and simplified from the original process I have been trying to use.
Hopefully this helps give you some idea of what's being experienced. To re-iterate the issue, when applying a filter from a related table and applying the calculation item that translates the values for a particular displayed field, hidden rows show up with unexpected values. I'm looking for an explanation as to why and if there is a method I can utilise to get around this occurence.