I am getting unexpected results from a measure. What am I doing wrong?

[Anonymous]

What I am trying to accomplish:

I want to do a percent of parent calculation. 

We are counting the number of patients that are in a specific Line of Therapy, in a specific date range, for a given division.  I want to know what percent of those patients are on a specific drug.

The numerator is the count of patients that are in a specific Line of Therapy, in a specific date range, for a given division, who are taking a specific drug.

The denominator is the count of patients that are in a specific Line of Therapy, in a specific date range, for a given division.  We want to ignore the drug here.

What is happening:

When I select a specific drug the denominator of this measure is being sliced, but it should not be.

The measure for the denominator is as follows:

CALCULATE (

    DISTINCTCOUNT fctLineOfTherapy[ProjectCombinedDivisionMpi] ),

    ALLEXCEPT (

        fctLineOfTherapy,

        fctLineOfTherapy[LotName],

        fctLineOfTherapy[LOTStartDate],

        DivisionMask[DivisionMask],

        ProjectDefinition

    )

)

The visuals:

Before selecting a drug

Before choosing a drug

After Selecting a drug:

After choosing a drug

Notice that the blue section Above has gone from 11% to 23%.  This is caused because the denominator changed from 1,173 to 571.  The numerator is fine.

The Data:

I am not able to provide real data, but here is some faked data to give an idea what we are working with.  I have used a hash to hide some of the data as it contains PHI and cannot be shared.

Sample of Data

The data model:

Relevant portion of data model

[Anonymous]

Hi.

First of all, the model is almost surely wrong. I'd suggest you do some reading about how to create correct models. Bi-directional filtering should only be used when needed, not as a habit. It's not only because bi-directional filtering is slower. It's also because it's DANGEROUS and you'll be producing wrong numbers before you know it and will not even notice it. This is not a joke, mate.

Please note that you should NEVER EVER expose fact tables to the user. THey should ALWAYS be hidden and you should never slice by their columns. Slicing is done through dimensions only.

Once you have a correct model, it'll be easy to write the measure.

Also, what's the point in erasing the names of the tables? You think anyone can write a measure without knowing what the names of your tables are?

Please do yourself a favour: create a correct model and then you'll be able to write correct and predictable DAX.

Best
D

[Anonymous]

Thanks Darlove,

You have confirmed my suspicion that the modeling is causing us issues.  Do you have any suggested reading that may help me correct this?

[Anonymous]

The best course I've personally been through is the one on www.sqlbi.com (the one on creating models). Yes, it's not free but you'll not find anything better. All my knowledge comes from those two guys - Alberto Ferrari and Marco Russo - and they are the creators.

Also, when you want to remove all filters from a table, you just do:

calculate( [Measure], ALL( TheTable )).

So, if you want to remove the filter on a Drug dimension, you should use the above where TheTable = Drug.

Also, bear in mind that ALLEXCEPT does nothing if there is no explicit filter on the columns enumerated under it. This happens very often with fact tables. If they are sliced by dimensions, then there are no explicit filters on the columns. A good design is one where the fact table's columns are all hidden (can't be used to slice), only measures defined in the table are exposed. All slicing must be done through dimensions. Also, 99% of the time dimensions are connected to facts through 1:* with one-way filtering from the dimension to the fact table. There are very specific scenarios where cross-filtering should be used. Using it as a habit will almost immediately lead to creating numbers that'll be hard to understand and simply will be incorrect.

Best
D