DAX formula that returns columns based on subcolumn's value from smallest to highest

[volt26]

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!

[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 · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[volt26]

Thank you very much! This file does all I need. 
If someone else use this, be aware that the smallest key is entered manually with a static value of 1. In my case, I didn't mention it but the key varies from event to event so I needed to change this static value for "Data[KEY]=MIN(Data[KEY])"

Have a wonderful day!

[Ashish_Mathur]

You are welcome.

[rajendraongole1]

Hi @volt26  - Please check the attached pbix file. 

Hope this helps let me know if any,

[volt26]

Wow 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?