The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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?
Solved! Go to Solution.
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]
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]
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
32 | |
21 | |
16 | |
15 |
User | Count |
---|---|
117 | |
34 | |
30 | |
24 | |
21 |