topic Re: DAX to filter on hierarchy level in DAX Commands and Tips

DAX to filter on hierarchy level

MungBurger — Tue, 18 Jun 2019 03:12:25 GMT

I have a similiar question to these existing questions 1 2 3 - that are unanswered, so I'll ask it in a different way.

I'm working with almost only pre-aggregated data. I was working on creating a new measure that would only display values at the correct level

Measure = IF(HASONEVALUE('Fact'[PercentageValue]) , MAX('Fact'[PercentageValue]), BLANK())

This works well until you get to a visualisation and you want to use the heirachy functionality.

Since the default behaviour is to aggregate data, the parent levels have more than 1 value so nothing is displayed.

is it possible to write a measure that also filters the dataset to only the records at that level of the hierarchy?

So at level 1 the only fact records belong to the level 1 members and not their children?

parry2k — Tue, 18 Jun 2019 05:00:15 GMT

MungBurger — Tue, 18 Jun 2019 10:28:58 GMT

In the Excel file, I copied the hierarchy names to fill empty levels and that has sort of improved some functionality.

MungBurger — Sun, 23 Jun 2019 21:27:39 GMT

The most acceptable solution was

This does exactly what I needed it to do.

The only limitations are that it can only be used in Azure AS or in the Power BI Service.