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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
apatwal
Helper III
Helper III

How 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.

apatwal_0-1643820223382.png

 

How we can achieve this?

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @apatwal ,

I created some data:

vyangliumsft_0-1644368755461.png

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

vyangliumsft_1-1644368755464.png

2. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_2-1644368755486.png

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

vyangliumsft_3-1644368755491.png

 

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

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @apatwal ,

I created some data:

vyangliumsft_0-1644368755461.png

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

vyangliumsft_1-1644368755464.png

2. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_2-1644368755486.png

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

vyangliumsft_3-1644368755491.png

 

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

ValtteriN
Super User
Super User

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:

ValtteriN_0-1643821333047.png


Now create a slicer selection table. E.g.

ValtteriN_1-1643821365956.png


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:
ValtteriN_2-1643821415813.png


Select desired slicer:

ValtteriN_3-1643821442640.pngValtteriN_4-1643821456070.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.