How to achieve Filter Logic based on two columns using DAX

[apatwal]

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?

[Anonymous · Accepted solution]

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

[ValtteriN]

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/