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.
Hi,
I'm trying to create a new custom column for my table which it's gonna count the sort order from other collumns on some condition:
1. it's should be on the same date
2. it should be sorted on it's value on descending order
this is the image for what I want to do:
date_column | value_column | my_custom_column |
2019/06/14 | 8 | 3 |
2019/06/14 | 9 | 2 |
2019/06/14 | 10 | 1 |
2019/06/15 | 7 | 2 |
2019/06/15 | 8 | 1 |
I've tried this DAX code :
CALCULATE(COUNT('my_table'[value_column]),FILTER('my_table',AND([date_column]=MAX([date_column]),[value_column]<MAX([value_column]))))
but it's not returning the result that I want.
I wondering if there's someone who has a better idea for this.
regards,
Solved! Go to Solution.
hi @Ashish_Mathur ,
thank you very much for your help.
it's close from what I'm looking for, but the problem is, with your code rows with date earlier than 2019/06/14 would also be counted.
I need rows to be counted only on the same day
Hi,
This calculated column formula works
=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[date_column]=EARLIER(Data[date_column])&&Data[value_column]>=EARLIER(Data[value_column])))
Hope this helps.
hi @Ashish_Mathur ,
thank you very much for your help.
it's close from what I'm looking for, but the problem is, with your code rows with date earlier than 2019/06/14 would also be counted.
I need rows to be counted only on the same day
Hi,
Your formula is not the same as mine. You are missing the EARLIER() before the &&
sorry for that.
it works perfectly now!
thank you very much, really appriciate it!
regards,
You are welcome. If my reply helped, please mark it as Answer.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |