Forum Discussion

Shri's avatar
Shri
Frequent Visitor
6 years ago

OR condition between multivalue parameters

I am replicating   a power bi report on paginated Report Builder that has multiple slicers (or parameters), namely Sales Region, SalesType, State, Model_Name, Serial_Number, etc. When we run the repo...
report builder
saglamtimur's avatar
saglamtimur
Resolver II
6 years ago

Using OR operator is trick in Report Builder. I have recorded as short video showing how to achieve this.

 

Video link

 

Actually, trick part is modifying DAX query and use parameter properly in this query. For your reference here is the query, and let me explain how does Report Builder/PowerBI RS handles parameters with OR.

 

CALCULATE (
    [measure],
    UNION (
        CROSSJOIN ( VALUES ( T1[Column1] ), ALL ( T2[Column2] ) ),
        CROSSJOIN ( ALL ( T1[Column1] ), VALUES ( T2[Column2] ) )
    )
)

 

This is how to implement multi-column filter. There are other methods, but this one is suitable for injecting parameters (For other techniques please refer to this excellent article. We are using #1). 

 

Power BI Report Builder Query Editor generates this query (for sure its "AND")

 

EVALUATE SUMMARIZECOLUMNS(
RSCustomDaxFilter(@DimProductColor,EqualToCondition,[DimProduct].[Color],String), 
RSCustomDaxFilter(@DimProductSize,EqualToCondition,[DimProduct].[Size],String), 
"TotalSalesAmount", [TotalSalesAmount])

 

Then, when executing, engine re-writes it and uses selected parameters and injects them as "FILTER" (still uses AND)

 

EVALUATE SUMMARIZECOLUMNS(
FILTER (
        VALUES ( 'DimProduct'[Color] ),
        ( 'DimProduct'[Color] = "Black" )
            || ( 'DimProduct'[Color] = "Blue" )
            || ( 'DimProduct'[Color] = "Grey" )
    ) ,
    FILTER (
        VALUES ( 'DimProduct'[Size] ),
        ( 'DimProduct'[Size] = "L" )
            || ( 'DimProduct'[Size] = "M" )
            || ( 'DimProduct'[Size] = "S" )
            || ( 'DimProduct'[Size] = "XL" )
    ),
"TotalSalesAmount", [TotalSalesAmount])

 

So every RSCustomDaxFilter function is translated to corresponding "FILTER". If we change our query to apply this method (to use  OR), we get the result we want. Finally here is the query which is modified in the Power BI Report Builder Query Editor.

 

EVALUATE SUMMARIZECOLUMNS(UNION (
            CROSSJOIN (
                RSCustomDaxFilter(@DimProductColor,EqualToCondition,[DimProduct].[Color],String),
                ALL ( 'DimProduct'[Size] )
            ),
            CROSSJOIN (
                ALL ( 'DimProduct'[Color] ),
                RSCustomDaxFilter(@DimProductSize,EqualToCondition,[DimProduct].[Size],String)
            )
        ), "TotalSalesAmount", [TotalSalesAmount])

 

 Hope it helps.




If my post solved your problem, mark my post as a solution to help others to quickly find it and also please give it a 👍