How to get rid of Hierarchy level in a visual

[jaryszek]

Hello Guys,

My relationships are like here:

in Dim_date i have dates from 01.04.2025 to 30.04.2025.

I filtered SubMeterCategories in a matrix:

the issue is that in Fct table sum on 01.04.2025 for this specific meter category is 0,00 because there are not records in FctTable.

So it means that this 2,45 Eur amount is coming from MeterCategory field which is in upper hierarchy level (MeterCategory -> MeterSubCategory). 

Why? Why visual is doing this? And how to avoif this behaviour. If there is not metersubcategory i should see nothing in matrix...

So Dim_Date is bigger and slier shows range between 01.04.2025 to 30.04.2025 but for 01.04 there is no row in Fct table. 
But still it shows it but gor higher level of hierarchy. Which is not right, i want to show only MeterSubCategories...

How to fix it? 
Best,
Jacek

[v-hashadapu · Accepted solution]

Hi @jaryszek , Thank you for reaching out to the Microsoft Community Forum.

This is because Power BI preserves hierarchy structure. It displays parent-level totals (MeterCategory) even if no data exists for a specific subcategory on that date. Although your slicer filters Dim_Date to April 2025 and there's no data for Basv2 Series Windows on 01.04.2025, Power BI still shows it by aggregating available data at the parent level, resulting in a misleading total.

To fix this, use a measure that returns blank when no fact data exists for the selected subcategory and date. For example:

CostFiltered :=

VAR HasData =

CALCULATE (

COUNTROWS ( Fct_EA_AmortizedCosts ),

NOT ISBLANK ( Fct_EA_AmortizedCosts[CostInBillingCurrency] ) )

RETURN

IF (HasData > 0, SUM(Fct_EA_AmortizedCosts[CostInBillingCurrency]), BLANK())

Replace your current measure in the matrix with this one. Power BI will automatically hide rows that return blank. This ensures that only MeterSubCategory values with actual data for the selected period are shown, and parent-level values don't appear unless supported by detail-level rows.

If-function-dax

Create a matrix visual in Power BI

Matrix visual format settings

Matrix visual considerations and limitations

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[jaryszek]

this worked!! thank you

[FBergamaschi]

Jacek

I am not sure what to answer as I do not see the model fully. Can you please either load the pbix for inspection or show the full model? And put in evidence what is grouped in the visual (one colums only or more?) where the grouped columns come from, where is the column in the model on which you are doing a sum etc

Ad a not, the calendar table should always include full years, this has nothing to do with this specific issue but you might encounter problems in the future

Best

FB

[wardy912]

Hi @jaryszek 

 You could use ISINSCOPE() in a measure to ensure it's only looking at subcategory.

MyMeasure :=
IF (
    ISINSCOPE ( 'Dim_Meter'[MeterSubCategory] ),
    SUM ( 'Fct'[Amount] )
)

[jaryszek]

thank you very much, so it is usual power bi visual behaviour for hierachies?

Best,
Jacek