Calculation Groups, Relationships and Filters

[Aklys]

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.

[OwenAuger · Accepted solution]

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

[Aklys]

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.

[OwenAuger]

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 = 1

2. 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

[Aklys]

I'm extremely greatful for the explanation. It gives me a better understanding of what's going on. Especially with creating that measure outside the calculation group.

One last question. The formula I'm using is more dynamic as it is to save time by addressing multiple columns in multiple tables. So the original formula is using SELECTEDMEASURE but as ISEMPTY requires a table, is there an equivalence for the table? For the purpose that I'm using the calculation group i can't hard code the table name of the SELECTEDMEAUSRE.

Thanks again so much for giving me the explanation, it was very helpful.

[OwenAuger]

You're welcome 🙂

You raise a good point. The basic ISEMPTY check only works if you know which table is being aggregated within SELECTEDMEASURE ().

There is no built-in way to determine which table a measure aggregates or references, since a given measure could reference several tables, or even no tables.

One possible solution would be to follow a measure naming convention that includes the table name in measure names.

In calculation items, you could then use SELECTEDMEASURENAME() to identify the table and determine whether the relevant fact table is nonempty.

For example, if you included the table name in brackets in the measure names, it might look like:

-- Calculation Item: KPIs__Display
VAR MeasureName =
    SELECTEDMEASURENAME ()
VAR FactTableNonEmpty =
    SWITCH (
        TRUE (),
        CONTAINSSTRING ( MeasureName, "(Book1)" ), NOT ISEMPTY ( Book1 ),
        CONTAINSSTRING ( MeasureName, "(Book1_without_Nulls)" ), NOT ISEMPTY ( Book1_without_Nulls )
    )    
RETURN
    IF (
        CONTAINSSTRING ( SELECTEDMEASURENAME ( ), "*KPI*Display*" ),
        VAR MeasureValue = SELECTEDMEASURE ( )
        RETURN
            IF (
                FactTableNonEmpty,
                SWITCH (
                    MeasureValue,
                    9000, "High Risk",
                    9001, "At Risk",
                    9002, "On Track",
                    "Unknown"
                )
            ),
        SELECTEDMEASURE ( )
    )

This is just an idea - there could be a better method! 🙂

[OwenAuger]

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.

[Aklys]

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.