Forum Discussion

dosania's avatar
dosania
Icon for Helper II rankHelper II
3 years ago

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

 



Need Help

1 Reply

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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
)

     

    Best Regards,

    Gallen Luo