Filter measure with 3 slicers/3 levels

[ACI_Data]

Hi,

This is my situation and hopefully somebody knows something.

I have a tab maintenance with a measure that divides the sum of maintenance of the latest year with the sum of revenue of the latest year

GO tov omzet = 
DIVIDE(
    CALCULATE(
        SUM('GrootOnderhoud'[Gerealiseerde groot onderhoud]);
            FILTER(
                ALL(GrootOnderhoud[Jaar]);
                GrootOnderhoud[Jaar]=
                    MAX(GrootOnderhoud[Jaar])
            )
    ); 
    CALCULATE(
        SUM(Omzet[Gerealiseerde omzet]);
            FILTER(
                ALL(Omzet[Jaar]);
                Omzet[Jaar]=
                    MAX(Omzet[Jaar])
            )
    );0)

In the revenue tab I have a measure that divides the sum of revenue of the latest year with sum of total rentable m2

Omzet per m2 huurder = 
DIVIDE(
    CALCULATE(SUM(Omzet[Gerealiseerde omzet]);FILTER(ALL(Omzet[Jaar]);Omzet[Jaar]=MAX(Omzet[Jaar]))); 
    SUM(Huurder[Totale verhuurbare m2]);
    0
)

So far so good. Both tabs have 4 slicers. Year, tenant, owner and premise. Now this is where it goes wrong.

When, in either tab, I filter using the slicer owner and/or premise everything is calculated correctly. The moment I use the slicer tenant the denominator of each measure doesn't change. Even though the value should change.

The order in revenue/maintenance is the owner(verhuurder) -> premise(pand) -> tenant(huurder).revenue lv 1

here you see the denominator of the total rentable m2 of the selected owner

revenue lv 2

the total rentable m2 of the selected owner and premise

revenue lv 3

here when selecting a specific tenant the total rentable m2 shouldn't be 805 m2 but 186 m2. This is in the tab revenue

Its the same with the measure in the tab maintenance

lv 1

total revenue of selected owner

lv 2

total revenue of selected owner and the premise

lv 3

When selecting a specific tenant the total revenue shouldn't be 127.596 euro but 41.364 euro

I have no idea what to do. It seems as if the measure stops filtering the data until the premise, after that it doesn't recognise the tenant(huurder) slicer. Is it a relationship? Should I do a calculated column? Is it not possible in BI to filter a measure on 3 levels? Should I change the measure?

Thanks in advance 😄

Desperate developer

[ACI_Data · Accepted solution]

It took me a while, but my data model and relationships were the main cause of this issue with filtering and measures. Guess I learned something new with this project 😄 thanks for the responses/help

[ACI_Data · Accepted solution]

It took me a while, but my data model and relationships were the main cause of this issue with filtering and measures. Guess I learned something new with this project! thanks for the responses/help 😄

[ACI_Data · Accepted solution]

It took me a while, but my data model and relationships were the main cause of this issue with filtering and measures. Guess I learned something new with this project 😄 thanks for the responses/help

[v-easonf-msft]

Hi, @ACI_Data 

Is it convenient for you to upload a simplified version of the sample?

It seems that there is a lack of information about some related measures, such as how the formula of the measure Huurder[Totale verhuurbare m2] is.

Best Regards,
Community Support Team _ Eason

[parry2k]

@ACI_Data well I hope someone can help. 

[parry2k]

@ACI_Data Way too long of a post to follow. I would recommend putting something together in a sample pbix file and the expected result, it will be much quicker to understand and resolve the problem.

[ACI_Data]

@parry2k But the data is of a customer and confidential I can't use it for a sample pbix. Creating a replica of the situation with self made data while making sure it portraits my report would actually take more time?