cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dosania
Helper II
Helper II

Help with enabling 2 dynamic filters to create end result

Hi,

I would like to introduce some dynamic filtering into my dashboard, where I have 2 filters that help select the final value shown. The first filter should narrow down the range the second filter gives the options users can see. I’m having trouble writing the first piece of DAX which will narrow down the selection, and the second which selects the subset from the first subset.
I started with the second filter, which shows the sales type, but the team also then wanted to split this to see Sales including/excluding commission


The first set of data/measures looks like the following;

 

NAMEFIELDFILTER NAME
Gross IncGross sales inc commissionInclude Commision
Gross ExGross sales ex commissionInclude Commision
Net IncNet sales inc commissionInclude Commision
Net ExNet sales ex commissionInclude Commision
Signed IncSigned sales inc commissionInclude Commision
Signed ExSigned sales ex commissionInclude Commision

 

 

Sales Commision =
var _commisionType = SELECTEDVALUE('Include Commision'[ParameterSelect])

RETURN
switch(_commisionType, "Yes", [Sales Inc Commision],
"No", [Sales Ex Commision],
[Blank])



The subset of the first selection will look like the following;

 

NAMEMEASURE NAMEFILTER NAME
Gross SalesGross SalesSales Type
Net SalesNet SalesSales Type
Signed SalesSigned SalesSales Type

 

Reporting Sales =
var _salesType = SELECTEDVALUE('Sales Type'[Name])

RETURN
switch(_salesType, "Gross Sales", [Gross Sales],
"Signed Sales", [Signed Sales"],
"Net Sales", [Net Sales],
[Blank])

 


I would appreciate it if someone could help me right the DAX for the first filter section and use the second for the first subset to get to my final result that would dispay on the Cards and Tables depending on the 2 filters the user has selected.

 

Thanks

 



1 REPLY 1
v-jialluo-msft
Community Support
Community Support

Hi @dosania ,

From your description, I know that you need to sift through the data twice and then come up with the result of the final payment.

But you need to have the corresponding sample data and the corresponding expected output to get better help.

You can upload sample data to OneDrive or Google Cloud Drive with a download link.

You can first try filtering the data using measure and filtering:

flag =
IF (
    MAX ( 'Sales'[name] ) IN VALUES(slicer[name] ), 1,0
)

vjialluomsft_0-1669712425672.png

 

Best Regards,

Gallen Luo

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.