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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Unexpected behawior of measure when filtering

Hi All


I struggle with an issue, which happens when filtering my table.

I have a table with top 20 brands filtered and few KPI's.

This seems to be ok...

The issue starts when I try to filter out marketing_concepts with names starting from "nivea cream all"

I am sure, these marketing_concepts corresponds to only one brand - NIVEA so there are no such concepts under any other brand. I expect only values for Nivea brand should change but (I marked it with red) all values for SALES $ (PY) and

SALES $ (vs PY) change too...



here is a syntax for SALES $ (PY):

SALES $ (PY) =
    [SALES $],
    FILTER (
        ALL ( AVON_NIELSEN_DATABASE[year] ),
            = MAX ( AVON_NIELSEN_DATABASE[year] ) - 1

and a samples of data (it is fake, do not worry):


Pbix file:  link

Source table: link


My questions:

1) why does it happen?

2) how to make my measures working well in second scenraio?


Thanks in advance

Community Champion
Community Champion



Remove the year of Page Filter and Add a Slicer with Date of DateTable (Herarchy - Year)



Lima - Peru

View solution in original post

Community Champion
Community Champion



i make changes in your model:


Add a Calculated Column "DateREF" in your table:


-Dateref = DATE(AVON_NIELSEN_DATABASE[year],1,1)


Create a DateTable 




Related both tables (Date-DateRef)


SALES $ (PY) = CALCULATE([SALES $],DATEADD(DateTable[Date].[Date],-1,YEAR))


The Visual look like this:




Lima - Peru


Thanks. I have followed your instruction step by step and got blanks in the final measure:



My latest Pbix file: (Sales vs PY issue v3): link


Am I doing sth wrong?

Could you please explain why original filters do not work? Is it a bug? 

Now I feel unsure using Power BI filters



Community Champion
Community Champion



Remove the year of Page Filter and Add a Slicer with Date of DateTable (Herarchy - Year)



Lima - Peru



The error in your measure exactly don't know, but i recommend to use this functions or Time Intelligence Functions.


SamePeriodLastyear gives you in this case the same result.

Lima - Peru

@ Victor


It is working now. 

One more question. What if I do not want to use a slicer? May I do it different way?





In spite of there is a workaround for this issue I still consider this as a bug.

From final user perspective it may lead to serious misinterpretation data as it was in my case

@ PowerBI team 

Can we expect to get it fixed?



Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors