Using SELECTEDVALUE function when more than one value is selected in Filter

[rapingali]

Hi All,

I Have a requirement to display total sales, total comission amount for a selected year. We have two different dates Order Date, Commision Paid Date and two measures  SalesAmount, Comission Amount. Though both Sales Amt and Comission Amt are linked at Order ID level (which is my datamodel join), on report we need to display total Sales amount, total comission amount paid for selected year. i.e, for 2021 , if there are 100 orders received woth sales $1000 and we paid comisson $250 in 2021 (it could be for orders received in 2021 or before), then report must be displayed like below.

Year State Sales Amount Commision Amount

2021 RI    $1000              $250

And my user wants to have YEAR filter (slicer) on Commision Issue Date column. So, I created a slicer on "Commion Issue Date" column and displayed it as YEAR column, and directly used "Comision Amount" column as it gets filtered. For "Sales Amount" I created DAX formula to ignore YEAR flter as "Sales Amount" shouldn't get filtered for only those that got a commision amount, rather consider all SALES amount ifor 2021.

Sales_Amount =

VAR COMIISION_YEAR = SELECTEDVALUE('Commision_Table'[Commision Year])

RETURN

CALCULATE(

Sum('Sales_Table'[Sales_Amount]),

REMOVEFILTERS('Commision_Table'),

FILTER ('Order_Table', 'Order_Table'[Order Year] = VALUE(COMIISION_YEAR))

)

This is perfrectly working as log as I select 1 value in YEAR filter. But, if I select more than one value for YEAR filter, my variable is stroing NULL values, hence sales amount is displaying as NULL.

Please help how to capture filter values, when we select more than one value is selected.

Thank you,

Radha Pingali

[parry2k · Accepted solution]

Well, what is the point of having a model which cannot do a simple thing? There is always an argument, to improve a model to simplify DAX or write complex DAX to achieve the results, and seems you are falling into 2nd category, which is usually not a good idea because it can have a performance impact and a solution which cannot be scaled over time.

Having said that, you can try this DAX function.

Sales_Amount =

CALCULATE(
Sum('Sales_Table'[Sales_Amount]),
REMOVEFILTERS('Commision_Table'),
TREATAS ( 
   VALUES('Commision_Table'[Commision Year]),
   'Order_Table'[Order Year]
)
)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[rapingali]

Thank you. The DAX function TREATAS really worked. 

[rapingali]

Thank you for your inputs. We tried creating date dimension, but can't do it due to exitsing data model limitations. So, we had to choose this other way around. 

Can you help me to find anyways to capture multiple filter values and pass on to filter a column?

[parry2k]

@rapingali you should create a date dimension in the table and then use that to link with both the tables (commission and sales) and in visual use date-related columns from the date dimension. This will follow the best practices.

You can follow my video here on time intelligence and create basic date table: Learn basics of Time Intelligence DAX functions - Part 1 - YouTube

, {"Start Date", type date}