Forum Discussion

andymcgurty's avatar
andymcgurty
Frequent Visitor
6 years ago
Solved

how to apply multi dimension member filter dynamically using IN operator

I'm trying to dynamically apply a filter statement in DAX using the IN operator. By passing in a string of members to filter on.    In my scenario the fact table and DIM table do not have a relatio...
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi andymcgurty 

     

    Try the below.

    Measure = 
VAR __view = VALUES( DIM_VIEW[View] )
RETURN
    CALCULATE(
        SUM( FACT_TB[amount] ),
        FACT_TB[View] IN __view
    )

    or a version with TREATAS for better performance

    Measure = 
VAR __view = VALUES( DIM_VIEW[View] )
RETURN
    CALCULATE(
        SUM( FACT_TB[amount] ),
        TREATAS( __view, FACT_TB[View] ) 
    )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

Hi andymcgurty 

 

Try the below.

Measure = 
VAR __view = VALUES( DIM_VIEW[View] )
RETURN
    CALCULATE(
        SUM( FACT_TB[amount] ),
        FACT_TB[View] IN __view
    )

or a version with TREATAS for better performance

Measure = 
VAR __view = VALUES( DIM_VIEW[View] )
RETURN
    CALCULATE(
        SUM( FACT_TB[amount] ),
        TREATAS( __view, FACT_TB[View] ) 
    )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

andymcgurty's avatar
andymcgurty
Frequent Visitor
6 years ago

good work Mariusz !

 

TREATAS() is exactly what I was looking for and is working with context filters in my analysis. 

 

Thanks

 

Andy