Forum Discussion

Aklys's avatar
Aklys
Helper I
2 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

OwenAuger's avatar
OwenAuger
Super User
2 years ago

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's avatar
Aklys
Helper I
2 years ago

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.