Forum Discussion
Using SELECTEDVALUE function when more than one value is selected in Filter
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.
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.
4 Replies
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}
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?
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.
Thank you. The DAX function TREATAS really worked.
