cancel
Showing results for
Did you mean:
Post Prodigy

## Compare multiple conditions against min and max values?

Can we compare multiple conditions against min and max dates as per two rows for example I have various id's against which I have a color column and date column now if we take 1 id let say 1234, if on min date color column is not equal to red and on max date color column is equal to red then give me 1 assigned in the column if not then 0 should be assigned any ideas how can this be done?

1 ACCEPTED SOLUTION
Super User II

if you want to create a measure ,try this

Measure =
VAR _mincolor=maxx(FILTER('Table','Table'[ID]=MAX('Table'[ID])&&'Table'[Date]=min('Table'[Date])),'Table'[Color])
VAR _maxcolor=maxx(FILTER('Table','Table'[ID]=MAX('Table'[ID])&&'Table'[Date]=max('Table'[Date])),'Table'[Color])
return if(_mincolor<>"Red"&&_maxcolor="Red",1,0)

Proud to be a Super User!

25 REPLIES 25
Super User II

Hey @Anmolgan ,

please take the time to create a pbix file that contains sample data but still reflects your data model, upload the file to onedrive or dropbox and share the link.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Post Prodigy

@TomMartens

Below is my dataset

 ID Color Date 23 Blue 31-07-2019 23 Red 31-07-2020 23 Red 31-07-2021 12 Red 01-06-2019 12 Red 01-06-2020 12 Red 01-06-2020 11 Blue 02-05-2020 10 Blue 02-05-2021 10 Blue 02-07-2021

Now according to what I explained earlier there should be 1 assigned on the Max date of "23" ID, according to the conditions explained, rest should be assigned to 0.

Does above makes sense to you? Is this possible with Power BI? if yes then please share some examples to get this done

Super User III

Hi,

On the table that you have shared, show the expected result very clearly.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Prodigy

@Ashish_Mathur  Expected result will only give "1" against "23" ID because it full fills the below conditions:

if on min date color column is not equal to red and on max date color column is equal to red then give me 1 assigned in the column if not then 0 should be assigned

Super User II

is this what you want?

Column =
VAR _min=min('Table'[Date])
VAR _max=max('Table'[Date])
return if('Table'[Date]=_min&&'Table'[Color]<>"Red" || 'Table'[Date]=_max&&'Table'[Color]="Red",1,0)

Proud to be a Super User!

Post Prodigy

@ryan_mayu I think, if this is written in DAX Measure format then this will work for my case but am not sure how to write this as custom measure? Because I cannot pass AND operator in Measure.

Post Prodigy

@ryan_mayu Something like this, in my case it gives me "1" even on the multiple rows where the color colum is red and has many rows but no values on max date changes besides the red think of is as below case:

 id color date 21 red 01-01-2000 21 red 01-01-2020 21 red 01-01-2021

in the above case 1 should not be assigned anywhere because the conditions result set is still false. We want to compare min and max as per individual ID's and not on the whole ID's min and max

Super User II

if you want to create a measure ,try this

Measure =
VAR _mincolor=maxx(FILTER('Table','Table'[ID]=MAX('Table'[ID])&&'Table'[Date]=min('Table'[Date])),'Table'[Color])
VAR _maxcolor=maxx(FILTER('Table','Table'[ID]=MAX('Table'[ID])&&'Table'[Date]=max('Table'[Date])),'Table'[Color])
return if(_mincolor<>"Red"&&_maxcolor="Red",1,0)

Proud to be a Super User!

Post Prodigy

@ryan_mayu This is working as a custom column and not as a measure, anything we can do to make it work as a DAX Measure.

Super User II

i think I provided the solution of creating a measure and you confirmed it works. Is there anything else that you need?

Proud to be a Super User!

Post Prodigy

@ryan_mayu
Yeah wanted to check if this can be createad as a Measure, I know you said that it can work as a measure, but this is not working as a measure, only as a custom column.

Super User II

you have already accepted the measure solution as an accepted solution.

Proud to be a Super User!

Post Prodigy

@ryan_mayu am not denying the fact that the solution that you have given works, I am just asking if this can be created in a mesaure thats all, if we cannot create it in a measure then thats also ok with me, I tried to create a custom column and use your solution inside of it and it works perfectly, but there are some limitations on my end with respect to visualizations, and just looking to get this working in a mesaure, please do not take in a negative way your solution obviously works.

Super User II

just want to figure out what exactly you want. I provided a custom column solution and a measure solution. do you mean you want to improve the measure solution?

Proud to be a Super User!

Post Prodigy

@ryan_mayu Measure solution is something that does not work, and custom column solution does work in my model.

Super User II

ok, it's better to provide the pbix file or pls clarity the issue you are facing.

Proud to be a Super User!

Post Prodigy

@ryan_mayu

https://ifitech-my.sharepoint.com/:u:/g/personal/anmol_ganju_ifi_tech/ESBUXLU3H1xPiAO7AXeYgJEBB8WyuX...

attached is the PBIX, Customer SKID is my ID here, Color column is Manufacturer Name in the model and Date column is Purchase Date in my model. You can check the file attached.

Super User II

what's the calculation logic for your sample file? Which column did you use my solution to create?

Proud to be a Super User!

Post Prodigy

@ryan_mayu let me check the file

Post Prodigy

https://ifitech-my.sharepoint.com/:u:/g/personal/anmol_ganju_ifi_tech/EQRIoPaqTf9Gn2UgTPIe0a0B9Ctoj0...

The measure that I have created is named as Acquisition 1, and Acquisition is the one working as a custom column, now custom column is giving correct result as 5 where as measure give me 0, it should give me 5.

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.