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

Aklys's avatar
Aklys
Helper I
2 years ago

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's avatar
OwenAuger
Super User
2 years ago

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! 🙂