Return value from a filtered dimension which is not in that dimension type.

[Anonymous]

Hi all, 

I have a star schema model, with some simple dimensions; DimDate, DimSite, and DimProduct.

I need to subtract and then add some values that are not found under the filter context when selecting SiteType. So for example I need to return the SUM of FactSales with a filter in the report slicers with DimDate (e.g) DimDate[IsPreviousMonth] = 1 and DimSite (e.g) DimSite[SiteType] = "Depot", but then take this and add a SUM from a separate hard-coded measure which is under a different SiteType, for example DimSite[SiteType] = "Office". 

I've gotten so far with ALL, however when I filter the report via DimSite[SiteType] it will not return a number and gets filtered out.

I am terrible at trying to explain scenarios like this so any questions to make things clearer please let me know.

Thanks,

Gordon

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can try to use below measure formula if it suitable for your replacement:

Measure =
VAR Selected =
    CALCULATE (
        SUM ( Fact[Sales] ),
        FILTER ( ALLSELECTED ( DimDate ), DimDate[IsPreviousMonth] = 1 ),
        DimSite[SiteType] = "Depot"
    )
VAR Other =
    CALCULATE (
        SUM ( Fact[Sales] ),
        FILTER ( ALLSELECTED ( DimDate ), DimDate[IsPreviousMonth] = 1 ),
        FILTER ( ALL ( DimSite ), DimSite[SiteType] = "Office" )
    )
RETURN
    selected + Other

If above not help, please share some sample data for testing and coding formula.

Regards,

Xiaoxin Sheng