Forum Discussion
Helper IDAX formula that returns columns based on subcolumn's value from smallest to highest
Hello!
I need to return columns but the data needed varies on a subcolumn named KEY. Each EVENT can have multiple KEY entries. If KEY is the lowest of EVENT, the formula needs to take the data from column START. If KEY is the highest of EVENT, it needs to take the data from column END. For erverything else, it needs to take the data from MIDDLE.
To help figure out what's needed, I summarized things with this Excel table :
If we look at EVENT entry 'C'
The desired result should somehow calculate this :
lowest KEY of EVENT = 35
highest KEY of EVENT = 26
everything else = 14 + 67
Desired result is = 35+26+14+67=142
In other words, I think the right formula would be something like :
START of lowestKEY + ( SUM of MIDDLE for all keys then substracts MIDDLE of lowestKEY and also substract MIDDLE of highestKEY ) + END of highestKEY
=>
35 + ( (76+14+67+5)-(76+5) ) + 26
I'm not quite familiar with DAX but I learn here and there with tutorials. I couldn't find answers for this specific issue. Thanks for your time!
Hi,
PBI file attached.
Hope this helps.
7 Replies
- rajendraongole1
Super User
volt26Wow that is very impressive!
Unfortunately, I get this error in my real file :
It works well when I select one single EVENT but when I select multiple ones, it doesn't.
The demo file provided doesn't return the SUM of all the EVENTS either.
Do you think it is possible to add this feature? I've never been closer to my goal before you gave me this!
I wonder if the formula has to be in the table instead of in a measure?
- Ashish_Mathur
Hi,
Share data in a format that can be pasted in an MS Excel file.
- volt26
Here it is :
EVENT KEY START MIDDLE END A 1 50 6 18 A 2 23 31 41 A 3 45 18 13 B 1 72 65 4 B 2 9 17 90 C 1 35 76 43 C 2 8 14 21 C 3 66 67 98 C 4 85 5 26 It will most likely be possible to copy-paste it. 🙂
- Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
