Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |