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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors