cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shri
New Member

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 report values to the parameters needs to be passed to view the report. For better understanding , it is like

 

[Sales Region] AND [SR_Type] AND [State] AND [Model_Name] AND [Serial_Number]

 

Whereas the requirement is that the end user should be able to pass value of ANY of the parameters (not all ) to be able to view the report. So, the requirement is like

 

[Sales Region] OR [SR_Type] OR [State] OR [Model_Name] OR [Serial_Number]

 

 

 

In SSRS, the same can be done by writing a simple SQL query like:-

 

Select [Sales Region], [SR_Type], [State], [Model_Name], [Serial_Number]

From <table>

Where [Sales Region] In @pSalesRegion OR   [SR_Type] In @pSRType OR [Model_Name] In @pModelName

 

Here the @pSalesRegion, @pSRType And  @pModelName represent the multiple value parameters.

 

 

What is the DAX equivalent of the Sql Query shown above? How can I achieve OR condition between various multivalue parameters? Thank you

7 REPLIES 7
saglamtimur
Resolver II
Resolver II

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 👍

Hi,

I found your solution to be useful, but I am not clear how I can modify it to suit my requirement. My current report builder query is as below:

EVALUATE SUMMARIZECOLUMNS('DataforPL'[CNTR], 'DataforPL'[CNTR_DT], 'DataforPL'[SUPP_LD_NO], RSCustomDaxFilter(@DataforPLCNTR,EqualToCondition,[DataforPL].[CNTR],String), RSCustomDaxFilter(@DataforPLCNTRDT,EqualToCondition,[DataforPL].[CNTR_DT],DateTime), RSCustomDaxFilter(@DataforPLSUPPLDNO,EqualToCondition,[DataforPL].[SUPP_LD_NO],String))

 

I have 3 parameters in above query above. I need to modify it such either I enter a combination of [CNTR] & [CNTR_DT] or enter just enter [SUPP_LD_NO] as the parameter, leaving other two fields blank.

 

Hope I was clear with my requirement. Thanks to assist.

 

Regards,

Muralidhar

Hi Murali,

   Try like below. You may need to handle "ALL" (blank) is no value is passed. 

SUMMARIZECOLUMNS (
        'Data'[CNTR], 'Data'[CNTR_DT], 'Data'[SUPP_LD_NO],
FILTER (
Data,
(FORMAT ( Data[CNTR_DT], "YYYY-MM-DD" )
<= FORMAT ("2020-01-01", "YYYY-MM-DD" )
|| (PATHCONTAINS ( "A", DATA[CNTR] )))
|| PATHCONTAINS ("1ABC", DATA[SUPP_LD_NO])
))
 
Thanks
Hari

Thank you @Hariharan_R !

If my answer helps you, Mark my post as a solution!!

amitchandak
Super User
Super User

@Shri , a filter like this

filter(all(Table),
Table[Sales Region] in values(Table[Sales Region]) || Table[SR_Type] in values(Table[SR_Type])
|| Table[State] in values(Table[State) || Table[Model_Name] in values(Table[Model_Name])
|| Table[Serial_Number] in values(Table[Serial_Number]))

 

But better have slicer from disconnected table to avoid all

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
KBO
Memorable Member
Memorable Member

Hi @Shri ,

you can use the filters in the filter pane in Power BI Desktop. Or is this too easy ...

https://docs.microsoft.com/de-de/power-bi/consumer/end-user-report-filter

 

Best,

Kathrin

 

 

 

 

If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!