Forum Discussion
Calculation Groups, Relationships and Filters
I'm looking for an explanation for why this occurs and maybe a solution if there is one.
Setup:
I have two tables that are related to each other and a calculation group/item. I use one table as a filter for a slicer to restrict what is displayed. I use the other table to supply information for display on a table visual. I use a calculation item to adjust the value of a column to a more displayable reference (using the calculation item as there are a number of fields that will have the same conversion necessary). The calculation item is just doing a switch statement to check the value of a SELECTEDMEASURE and output the appropriate value.
Problem:
While the calculation item is not applied the filter works without any issues. As soon as the calculation item is applied it displays all rows, with incorrectly applied translations for the field it is adjusting the display on for the rows that should still be hidden due to the slicer selections.
Temp Solution:
I can merge the two tables into one and filter on those values. Not preferable, but it did help demonstrate it's an issue between table relationships and the application of calculation items.
So hoping someone can explain clearly to me something I haven't been able to find a clear explanation for. What is the conflict between calculation items and table relationships? Also is there some trick to getting around this conflict?
Thanks in advance for any direction
EDIT:
I have look at most of the SQLBI articles in relation to Calculation Groups but haven't found a good explanation there yet.
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
7 Replies
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
Thanks OwenAuger for the solution.
Hope you don't mind me seeking some further clarification on the why though. I'm still a bit confused by the converting blanks to non-blanks, as I feel like I'm missing a grasp on some fundemental dax behaviour. There are no null values or values unaligned with the table relationship for the second page example, so just wanting to understand what is generating the blank values. Are records that that are not shown due to filters generating blank values as the nature of not being shown?
There is still one problem that the solution creates and that is sometimes a record may contain a value that needs to show up as unknown because it is a null value. The solution you provided doesn't actually deal with these appropriately (shown on table with nulls). How would you correct this beyond replacing null values with a new value (as this isn't feasible to adjust as null unfortunately has a particular purpose from it's source data as does the -1, terrible I know but no control over the source)?
I will have to read more on the auto-exist as I didn't understand the article provided, but thanks for directing me to that bit of knowledge.Thanks again for the partial solution and explanation.
Sure thing 🙂
Actually there are some pretty unintuitive things going on here!
One way to explain the behaviour of the "unexpected rows" is to forget about the calculation group (for the moment) and do the following:
1. Create a measure that always returns 1 (regardless of filters):
One = 12. On the page Table without null values, remove KPI_Display__without from the table and instead add the new measure One.
3. Also add Book2[Item_Type] to the table.
We have the odd results that
- Type_ID = 1 is paired with Item_Type = 1-3
- Type_ID = 2 is paired with Item_Type = 1-3
In other words, we have a crossjoin of sorts between the two related tables.
I'll admit this is pretty unintuitive!
A short explanation is that applying a filter to columns of a table on one side of a relationship (e.g. a dimension table) does not "eliminate" rows from a table on the other side of the relationship (e.g. a fact table) from the DAX query producing the visual.
However, the auto-exist feature does eliminate rows when columns of the same table are simultaneously filtered.
This behaviour is normally hidden when "normal" measures that aggregate rows of a fact table, since such measures produce blank results for non-existing combinations.
The behaviour of the calculation item was similar to the One measure in that it always produces a result of "Unknown" if no rows exist in Book1 for a given combination of filters.
On to your second point, I actually hadn't considered the case of a "valid" blank result from the KPI_Display measure(s), which as you rightly say can occur when a blank value is in the column being aggregated.
To handle this situation, I think it it would be better to replace
NOT ISBLANK ( KPIDisplay )with
NOT ISEMPTY ( Book1 )This ensures that a result is returned for "valid" blanks (or other valid values) resulting from aggregation of the fact table, but not from situations where there are no rows in the fact table (such as when Item Type Table =Type 1 and Item_Type = Type_2.
Hoping this helps!
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.
