cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anmolgan
Post Prodigy
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

@Anmolgan 

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)

1.PNG





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

Proud to be a Super User!




View solution in original post

25 REPLIES 25
TomMartens
Super User II
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

@TomMartens 

Below is my dataset


IDColorDate
23Blue31-07-2019
23Red31-07-2020
23Red31-07-2021
12Red01-06-2019
12Red01-06-2020
12Red01-06-2020
11Blue02-05-2020
10Blue02-05-2021
10Blue

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

 

@Anmolgan 

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)

1.PNG





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

Proud to be a Super User!




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

@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:

 

idcolordate
21red01-01-2000
21red01-01-2020
21red

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

@Anmolgan 

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)

1.PNG





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

Proud to be a Super User!




View solution in original post

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

@Anmolgan 

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

 





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

Proud to be a Super User!




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

@Anmolgan 

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

 

1.PNG





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

Proud to be a Super User!




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

@Anmolgan 

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? 

Please provide more info about the limitations you just mentioned and what's the expected output?





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

Proud to be a Super User!




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

@Anmolgan 

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





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

Proud to be a Super User!




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

@Anmolgan 

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





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

Proud to be a Super User!




@ryan_mayu let me check the file

@ryan_mayu Please check the attached link 

 

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors