Different measure depending on filters

[jeffshieldsdev]

I have a mixed grain problem.

In my model I have two fact tables: Unique Visits by Country, and Unique Visits by State.

These are both aggregated counts. Unique Visits by State is always accurate in visuals, but I can't merely SUM Unique Visits by State to get Unique Visits by Country because it results in double-counting--if there are two States with two Unique Visits, Unique Visits by the Country should be two--not four.

So I need to seperate tables in my model.

My challenge is in getting the right value to show in a hierarchial Matrix visual. This measure seems to work:

Unique Visits (measure) = CALCULATE(
    IF(
        ISFILTERED('Geo Hierarchy'[State]),
        SUM('Unique Visits by State'[Unique Visits]),
        SUM('Unique Visits by Country'[Unique Visits])
    )
)

But if I have a Report-level or Page-level filters on 'Geo Hierarchy'[State] than the Unique Visits by State will show in the Country-level of the matrix.

Is there a better approach, or how can I get my measure to ignore Report- or Page-level filters? Thank you.

Example: Mixed Grain Aggregation Problem_2022-10-05_1535.pbix

[jeffshieldsdev · Accepted solution]

I found this solution from Phil Seamark at DAX.tips

Creative Aggs Part I : Introduction - Phil Seamark on DAX

It uses INSCOPE() so I'm going to go with that.

Unique Visits (measure) ISINSCOPE = CALCULATE(
    IF(
        ISINSCOPE('Geo Hierarchy'[State/Prov]),
        SUM('Unique Visits by State'[Unique Visits by State]),
        SUM('Unique Visits by Country'[Unique Visits by Country])
    )
)

[jeffshieldsdev]

Maybe HASONEFILTER() is a better choice here. Only one filter will be present the lower-granularity rows of the Matrix visual, so this works:

Unique Visits (measure)3 = CALCULATE(
    IF(
        HASONEFILTER('Geo Hierarchy'[State/Prov]),
        SUM('Unique Visits by State'[Unique Visits by State]),
        SUM('Unique Visits by Country'[Unique Visits by Country])
    )
)

EDIT: This doesn't work in all situations either. Basically when at a State level in the hierarchy, I want to pull the value from the State table--otherwise I want to pull from the Country table.

[v-yueyunzh-msft]

Hi , @jeffshieldsdev 

If you want to ignore the 'Geo Hierarchy'[State] filter on your visual , you need to use ALL() function , like this:

Unique Visits (measure) = CALCULATE(
    IF(
        ISFILTERED('Geo Hierarchy'[State/Prov]),
        SUM('Unique Visits by State'[Unique Visits by State]),
        SUM('Unique Visits by Country'[Unique Visits by Country])
    ),ALL('Geo Hierarchy'[State/Prov])
)

Also for your question, you can provide us with the sample output data you want so that we can better help you.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jeffshieldsdev]

Thanks for the reply.

I shared a .pbix above.

The green column in my screenshot is what I should be getting. The measure does work, but when there is a Page- or Report-level filter, it does not because ISFILTERED() returns TRUE when there's a Page-/Report-level filter.