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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.