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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MrPaco
Regular Visitor

Filter not working in DAX statement when Filtered by other Measure But working when working with var

I have a report with statusses of suppliers. This info is not delivered daily but irregularly. 

I created this measure to calculate the correct amount of lines, that works on Year level, month level or day level:

 

Test =

CALCULATE(

  SUM(Fact_Cables_Hist[DefinedInDB]),

  FILTER(Dim_Date, Dim_Date[Date] = [DateUsedHistory])

)

 

The DateUsedHistory is the measure that finds the last date of values recieved depend on the filter context: 

 DateUsedHistory =

CALCULATE(

    MAX(Fact_Cables_Hist[Date]),

    FILTER(Fact_Cables_Hist ,Fact_Cables_Hist[Date] <= [MaxDate])

 

The 'Test' measure is working fine on day level, but when only months are shown, it takes all recieved that month into account.

 MrPaco_1-1654702931950.jpeg

When I do not use DateUsedHistory, but calculate it in the measure itself as a variable, the results are what I Expect.

 

 Defined Cables Actual =

VAR UseHis =

    CALCULATE(

      MAX(Fact_Cables_Hist[Date]),

      FILTER(Fact_Cables_Hist ,Fact_Cables_Hist[Date] <= [MaxDate])

    )

RETURN

    CALCULATE(

      SUM(Fact_Cables_Hist[DefinedInDB]),

      FILTER(Dim_Date, Dim_Date[Date] = UseHis)

)  

I already returned the value UseHis to check and it has the same value as DateUsedHistory.

The model is a simple one to many relation between Dim_Date and Fact_Cables_Hist.

Why is the filter working when is use a variable, but not when I use the measure to filter the Calendar table?

 

 

 

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

FILTER is an iterator, so when you are using the [DateUsedHistory] measure within the filter of the Test measure, it is being evaluated for every row of the date table. When you store the value in a variable first it doesn't recalculate for every row, it just compares the already computed value with Dim_Date[Date]

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

FILTER is an iterator, so when you are using the [DateUsedHistory] measure within the filter of the Test measure, it is being evaluated for every row of the date table. When you store the value in a variable first it doesn't recalculate for every row, it just compares the already computed value with Dim_Date[Date]

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors