cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Flash_BI Regular Visitor
Regular Visitor

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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @Flash_BI,

 

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

 

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
1 REPLY 1
Community Support Team
Community Support Team

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

Hi @Flash_BI,

 

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

 

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |