Forum Discussion
Helper IIIHow to achieve Filter Logic based on two columns using DAX
Hi,
I have a below requirements to add data into reports (filter logic); below is the scenario
We have two columns Profit % 2021 and Profit % 2020 based on these two columns we have to filter the records in report
1. Profit % 2021 < Profit % 2020; pull all the previous records along with current record onto report and use all those records to calculate total sales, revenue and other calculations.
2. Profit % 2021 > Profit % 2020; remove that customer and other details from report
3. Again if Profit % 2021 < Profit % 2020; pull all previous records into report and use all those records to calculate total sales, revenue and other calculations.
How we can achieve this?
Hi apatwal ,
I created some data:
From the data, [Profit % 2020] of CustomerA is greater than [Profit % 2021], and [Profit % 2020] of CustomerB is less than [Profit % 2021]
Here are the steps you can follow：
1. Create measure.
Flag =
IF( MAX('Table'[Profit % 2020])>MAX('Table'[Profit % 2021]),1,0)AllRevenue = SUMX(ALLSELECTED('Table'),[Revenue])The results have not been filtered
2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
After setting [Flag] to 1, according to condition 2, the data of CustomerB is deleted, leaving the data of CustomerA, and the value of [AllRevenue] will also calculate the filtered data
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
- ValtteriN
Community Champion
Hi,
I am not 100% if I follow but you want to filter values based on two columns in your visual? Here is one way to do this:
Test data:
Now create a slicer selection table. E.g.
Create a filter measure:Filter 2020<2021 =SWITCH(max('ProfitFilterS'[Value]),"2020<2021",if(MAX(ProfitFilter[Profit2020])<MAX(ProfitFilter[Profit2021]),1,0),"2020>2021",if(MAX(ProfitFilter[Profit2020])>MAX(ProfitFilter[Profit2021]),1,0))Apply it to visual:
Select desired slicer:I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/ Hi apatwal ,
I created some data:
From the data, [Profit % 2020] of CustomerA is greater than [Profit % 2021], and [Profit % 2020] of CustomerB is less than [Profit % 2021]
Here are the steps you can follow：
1. Create measure.
Flag =
IF( MAX('Table'[Profit % 2020])>MAX('Table'[Profit % 2021]),1,0)AllRevenue = SUMX(ALLSELECTED('Table'),[Revenue])The results have not been filtered
2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
After setting [Flag] to 1, according to condition 2, the data of CustomerB is deleted, leaving the data of CustomerA, and the value of [AllRevenue] will also calculate the filtered data
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
