Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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!
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
@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
Hi,
On the table that you have shared, show the expected result very clearly.
@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
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!
@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:
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
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!
@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.
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!
@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.
you have already accepted the measure solution as an accepted 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.
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?
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.
ok, it's better to provide the pbix file or pls clarity the issue you are facing.
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.
what's the calculation logic for your sample file? Which column did you use my solution to create?
Proud to be a Super User!
@ryan_mayu Please check the attached link
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |