DAX to pick first in table (already filtered by Selected Metric)

[jmillsjmills]

Having used Power Query to look to an Excel sheet that had grouped rows, I now have data in a slightly odd format. But I need to aggregate row data at each level.

So the hierarchy is SECTOR > INDUSTRY > SECURITY (you can see it labelled in the "Grouping" column)

Using the following DAX measure, I am filtering to a particular Metric as selected in a dropdown in the dashboard (the data for the sake of the screenshot is already filtered by a selected measure)..

This measure is currently using median but I want it to just return the FIRST row (when sorted ascending by column [Index]) based on this same selected metric feature in the DAX for each Sector in the [Sector] column.

Any help would be most appreciated!!

AvgMetricSectIndAttEquity =
VAR SelectedMetrics = VALUES('SelectedMetricEquityAtt'[Metric])
RETURN
MEDIANX(
FILTER(
'SectorInd v World',
'SectorInd v World'[Metric] IN SelectedMetrics
),
'SectorInd v World'[Value]
)

[jmillsjmills]

Thanks so much for coming back! I get this error:

A single value for column 'Value' in table 'SectorInd v World' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I think this is because my raw data looks like the below (previous screenshot was filtered). I need it to also be able to keep the "Time Horizon" filter that gets applied via a slicer within the dashboard... There is always one tile in the slicer select, so the data can be filtered by this selection too. Do you see what I mean?

THANKS SO SO MUCH 🙂

[oritam3210]

Sorry,
My mistake,

see correct measure

AvgMetricSectIndAttEquity =
VAR SelectedMetrics = VALUES('SelectedMetricEquityAtt'[Metric])
VAR FirstIndex = 
   CALCULATE(
      MIN('SectorInd v World'[Index]),
      'SectorInd v World'[Metric] IN SelectedMetrics
   )
RETURN
CALCULATE(
   SELECTEDVALUE('SectorInd v World'[Value]),
   'SectorInd v World'[Index] = FirstIndex 
)

 **This will work only if the Indes column is a unique identifier

---------------------------------------------------------------------------------------------------------------------
If you find this helpful, kindly consider marking it as 'Accepted Solution.'
This action helps others quickly find a reliable answer!

[jmillsjmills]

Should the SELECTEDVALUE() part be dealing with the Time Horizon part?

Shouldn't it be something like ALLSELECTED() ? [Index] column isn't a unique identifier btw - but as long as the data is filtered correctly it WILL be unique within that filtered data, so there will be one number that is the lowest in that column. Does that make sense?

It's not returning an error but it's producing blanks in my visuals..

[jmillsjmills]

Hi again - I really appreciate your help with this. What I'm technically trying to do with the measure is plot a simple chart/table that shows the first row for each sector. [Index] is really called [AttributeOrder] btw. But it populates blank with your measure (I'm sure your measure is working really! I'm just misunderstanding how to apply it)

[jmillsjmills]

Lovely! Have got there now. FWIW it's this I needed:

1stMetricSectIndAttEquity =

VAR SelectedMetrics = VALUES('SelectedMetricEquityAtt'[Metric])

RETURN

CALCULATE(

    MIN('SectorInd v World'[Value]),

    TOPN(1, 'SectorInd v World', 'SectorInd v World'[AttributeOrder], ASC),

    'SectorInd v World'[Metric] IN SelectedMetrics

)

Thank you so much again!!

[oritam3210]

Perfect!

Happy to help

---------------------------------------------------------------------------------------------------------------
If you find this helpful, kindly consider marking it as 'Accepted Solution.'
This action helps others quickly find a reliable answer!

[oritam3210]

Hi @jmillsjmills,
did you try something like this

AvgMetricSectIndAttEquity =
VAR SelectedMetrics = VALUES('SelectedMetricEquityAtt'[Metric])
VAR FirstIndex = 
   CALCULATE(
      MIN('SectorInd v World'[Index]),
      'SectorInd v World'[Metric] IN SelectedMetrics
   )
RETURN
CALCULATE(
   'SectorInd v World'[Value],
   'SectorInd v World'[Index] = FirstIndex 
)

Let me know if it worked

---------------------------------------------------------------------------------------------------------------------
If you find this helpful, kindly consider marking it as 'Accepted Solution.'
This action helps others quickly find a reliable answer!

[jmillsjmills]

Thanks so much for coming back! I get this error:

A single value for column 'Value' in table 'SectorInd v World' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I think this is because my raw data looks like the below (previous screenshot was filtered). I need it to also be able to keep the "Time Horizon" filter that gets applied via a slicer within the dashboard... There is always one tile in the slicer select, so the data can be filtered by this selection too. Do you see what I mean?

THANKS SO SO MUCH