Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.