Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jmillsjmills
Helper III
Helper III

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

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_0-1700761271298.png

 

8 REPLIES 8
jmillsjmills
Helper III
Helper III

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 🙂

 

jmillsjmills_0-1700772578337.png

 

 

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!

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..

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_0-1700774504685.png

 

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!!

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
Helper I
Helper I

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!

 

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 

jmillsjmills_1-1700773144270.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors