topic Re: DAX Formula for Drilling Down to the Min Value and maintain Level Hierarchy in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Formula-for-Drilling-Down-to-the-Min-Value-and-maintain/m-p/2603498#M75333
<P>Hi <LI-USER uid="114828"></LI-USER> ,</P>
<P> </P>
<P>Is this what you want? Please check the attached .pbix file.</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Icey_0-1656320165037.png" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/738758iE42812A20D161884/image-size/large?v=v2&px=999" role="button" title="Icey_0-1656320165037.png" alt="Icey_0-1656320165037.png" /></span></P>
<LI-CODE lang="markup">Filter Measure =
VAR MIN_ =
MINX ( ALLEXCEPT ( 'Table', 'Table'[Device] ), [Delta Measure] )
VAR MAX_ =
MAXX ( ALLEXCEPT ( 'Table', 'Table'[Device] ), [Delta Measure] )
RETURN
IF ( [Delta Measure] = MIN_ || [Delta Measure] = MAX_, 1 )
</LI-CODE><LI-CODE lang="markup">Delta Max =
MAXX ( ALLEXCEPT ( 'Table', 'Table'[Device] ), [Delta Measure] )</LI-CODE><LI-CODE lang="markup">Delta Min =
MINX ( ALLEXCEPT ( 'Table', 'Table'[Device] ), [Delta Measure] )</LI-CODE>
<P> </P>
<P> </P>
<P>Best Regards,</P>
<P>Icey</P>
<P> </P>
<P>If this post <STRONG>helps</STRONG>, then please consider <EM><STRONG>Accept it as the solution</STRONG></EM> to help the other members find it more quickly.</P>Mon, 27 Jun 2022 08:57:43 GMTIcey2022-06-27T08:57:43ZDAX Formula for Drilling Down to the Min Value and maintain Level Hierarchy
https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Formula-for-Drilling-Down-to-the-Min-Value-and-maintain/m-p/2598807#M75055
<P>I'd like to show a minimum value relationship per part.<BR /><BR />The data I'm working with has a four level data hierachy per group.<BR />Starting with the Group Name.<BR /><BR />I need the min value down to the lowest hiercharchy per group pre exercise. </P><P>The group is then exercised.<BR />I need the min value down to the lowest hiercharchy per group post exercise.</P><P> </P><P>I take the delta between pre and post exercise: Delta = min(table[Pre Value]) - min(table[PostValue])<BR /><BR />The problem arises is when I want to show this in a regular table. I have to add the columns of each data level in the hierarchy to show the smallest and largest delta. I don't want to show all the levels in the hierachy in the table. <BR /><BR />Basically show Group Name and Largest Delta and Smallest Delta.<BR /><BR />I need a measure that will maintain the data hierachry down to the lowest level.<BR /><BR />This is the only way I know how to get this to work. Is to reveal each data level in the hiearchy. (Drill Down)<BR />This measure works fine with the table below, but shows way too much unwanted information.<BR />Delta = min(table[Pre Value]) - min(table[PostValue])</P><TABLE border="1"><TBODY><TR><TD><P>Device</P></TD><TD>Level1</TD><TD>Level2</TD><TD>Level3</TD><TD>Level4</TD><TD>Pre Value</TD><TD>PostValue</TD><TD>Delta</TD></TR><TR><TD><P>A</P></TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1000</TD><TD>1050</TD><TD>-50</TD></TR><TR><TD>A</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>1</TD><TD>999</TD><TD>899</TD><TD>100</TD></TR><TR><TD>A</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1010</TD><TD>1000</TD><TD>10</TD></TR><TR><TD>A</TD><TD>1</TD><TD>1</TD><TD>0</TD><TD>1</TD><TD>800</TD><TD>770</TD><TD>30</TD></TR><TR><TD>B</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>900</TD><TD>925</TD><TD>-25</TD></TR><TR><TD>B</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>1</TD><TD>750</TD><TD>800</TD><TD>-50</TD></TR><TR><TD>B</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>800</TD><TD>750</TD><TD>50</TD></TR><TR><TD>B</TD><TD>1</TD><TD>1</TD><TD>0</TD><TD>1</TD><TD>575</TD><TD>550</TD><TD>25</TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P><BR /><BR />What I need is a measure to condense and not show each level but maintain the hiearchy to the lowest level.<BR />I do not know how to maintain or force hierchy level down to the lowest level using DAX.<BR /><BR />This is what I need ideally with Min Delta and Max Delta as separate measures.<BR />That way I can show both min or max Delta, or both.</P><TABLE border="1"><TBODY><TR><TD>Device</TD><TD>Level1</TD><TD>Level2</TD><TD>Level3</TD><TD>Level4</TD><TD>PreValue</TD><TD>PostValue</TD><TD>Delta Type</TD><TD>Delta</TD></TR><TR><TD>A</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1000</TD><TD>1050</TD><TD>min</TD><TD>-50</TD></TR><TR><TD>A</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>1</TD><TD>999</TD><TD>899</TD><TD>max</TD><TD>100</TD></TR><TR><TD>B</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>1</TD><TD>750</TD><TD>800</TD><TD>min</TD><TD>-50</TD></TR><TR><TD>B</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>800</TD><TD>750</TD><TD>max</TD><TD>50</TD></TR></TBODY></TABLE><P><BR />I would settle for the table below if it's any easier.</P><TABLE border="1"><TBODY><TR><TD>Device</TD><TD>Delta Min</TD><TD>Delta Max</TD></TR><TR><TD>A</TD><TD>-50</TD><TD>100</TD></TR><TR><TD>B</TD><TD>-50</TD><TD>50</TD></TR></TBODY></TABLE><P><BR />Note:<BR />PreValue is a measure.<BR />PostValue is a measure<BR /><BR />I've tried many DAX functions. Using All, All Except, All Selected, Filter and some combination thereof as well as other functions.<BR /><BR />I need help from the experts.<BR /><BR />Thank You.</P>Thu, 23 Jun 2022 20:26:34 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Formula-for-Drilling-Down-to-the-Min-Value-and-maintain/m-p/2598807#M75055roncruiser2022-06-23T20:26:34ZRe: DAX Formula for Drilling Down to the Min Value and maintain Level Hierarchy
https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Formula-for-Drilling-Down-to-the-Min-Value-and-maintain/m-p/2601763#M75214
<P>I would like like some help. Anyone care to chime in to at least discuss will be appreciated.<BR />I was thinking maybe a helper table to begin with? Maybe a helper table is not needed. I am unsure.<BR />Hope to hear from the forum. <BR /><BR />Thank You.</P>Sat, 25 Jun 2022 14:04:07 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Formula-for-Drilling-Down-to-the-Min-Value-and-maintain/m-p/2601763#M75214roncruiser2022-06-25T14:04:07ZRe: DAX Formula for Drilling Down to the Min Value and maintain Level Hierarchy
https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Formula-for-Drilling-Down-to-the-Min-Value-and-maintain/m-p/2603498#M75333
<P>Hi <LI-USER uid="114828"></LI-USER> ,</P>
<P> </P>
<P>Is this what you want? Please check the attached .pbix file.</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Icey_0-1656320165037.png" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/738758iE42812A20D161884/image-size/large?v=v2&px=999" role="button" title="Icey_0-1656320165037.png" alt="Icey_0-1656320165037.png" /></span></P>
<LI-CODE lang="markup">Filter Measure =
VAR MIN_ =
MINX ( ALLEXCEPT ( 'Table', 'Table'[Device] ), [Delta Measure] )
VAR MAX_ =
MAXX ( ALLEXCEPT ( 'Table', 'Table'[Device] ), [Delta Measure] )
RETURN
IF ( [Delta Measure] = MIN_ || [Delta Measure] = MAX_, 1 )
</LI-CODE><LI-CODE lang="markup">Delta Max =
MAXX ( ALLEXCEPT ( 'Table', 'Table'[Device] ), [Delta Measure] )</LI-CODE><LI-CODE lang="markup">Delta Min =
MINX ( ALLEXCEPT ( 'Table', 'Table'[Device] ), [Delta Measure] )</LI-CODE>
<P> </P>
<P> </P>
<P>Best Regards,</P>
<P>Icey</P>
<P> </P>
<P>If this post <STRONG>helps</STRONG>, then please consider <EM><STRONG>Accept it as the solution</STRONG></EM> to help the other members find it more quickly.</P>Mon, 27 Jun 2022 08:57:43 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Formula-for-Drilling-Down-to-the-Min-Value-and-maintain/m-p/2603498#M75333Icey2022-06-27T08:57:43ZRe: DAX Formula for Drilling Down to the Min Value and maintain Level Hierarchy
https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Formula-for-Drilling-Down-to-the-Min-Value-and-maintain/m-p/2605012#M75399
<P>Hi <LI-USER uid="164362"></LI-USER> <BR /><BR />I am looking at it now and trying to understand the logic behind your DAX measures and how it all works together. The data I posted in this thread is a basic format example of the real data but not the real data. The acutal data set is millions of rows deep. I will apply your measures to the real data and I will respond soon.<BR /><BR />Thank You for taking the time <LI-USER uid="164362"></LI-USER> </P>Mon, 27 Jun 2022 19:30:49 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Formula-for-Drilling-Down-to-the-Min-Value-and-maintain/m-p/2605012#M75399roncruiser2022-06-27T19:30:49Z