Forum Discussion
Filter issuev - Context transition
Hello, I have an issue with a measure not behaving as I would expect.
Fact Table:
| Date | MRR | Activated | Deactivated |
| 1/01/2023 | 45 | 1 | 0 |
| 1/02/2023 | 36 | 0 | 1 |
| 1/03/2023 | 75 | 1 | 0 |
| 1/04/2023 | 12 | 0 | 0 |
| 1/05/2023 | 21 | 1 | 0 |
| 1/06/2023 | 70 | 1 | 0 |
| 1/07/2023 | 76 | 1 | 0 |
| 1/08/2023 | 96 | 0 | 1 |
| 1/09/2023 | 62 | 1 | 0 |
| 1/10/2023 | 7 | 0 | 0 |
| 1/11/2023 | 69 | 0 | 0 |
| 1/12/2023 | 71 | 0 | 0 |
| 1/03/2024 | 62 | 1 | 0 |
| 1/03/2024 | 62 | 0 | 0 |
| 1/03/2024 | 40 | 0 | 0 |
| 1/01/2024 | 42 | 1 | 0 |
| 1/01/2024 | 60 | 0 | 0 |
| 1/01/2024 | 7 | 1 | 0 |
| 1/01/2024 | 88 | 0 | 1 |
| 1/01/2024 | 47 | 1 | 0 |
| 1/02/2024 | 7 | 1 | 0 |
| 1/02/2024 | 52 | 1 | 0 |
| 1/02/2024 | 73 | 0 | 1 |
| 1/02/2024 | 94 | 1 | 0 |
| 1/02/2024 | 77 | 0 | 0 |
| 1/03/2024 | 61 | 0 | 0 |
| 1/03/2024 | 96 | 0 | 0 |
| 1/03/2024 | 20 | 1 | 0 |
| 1/03/2024 | 65 | 0 | 1 |
| 1/03/2024 | 62 | 0 | 0 |
This Fact Table is linked to a Date table with columns:
- Date (unique key)
- IsBeforeThisMonth
- Year
- YYYY-MM
Measures:
- MEASURE _Measures[MRR] = SUMX('Table', 'Table'[MRR])
- MEASURE _Measures[New MRR] = CALCULATE([MRR], 'Table'[Activated] = 1)
- MEASURE _Measures[New MRR AVG Not Working] = AVERAGEX(VALUES('Date'[YYYY-MM]), [New MRR])
- MEASURE _Measures[New MRR AVG Working] = AVERAGEX(VALUES('Date'[Date]), [New MRR])
I have two page filters:
- filtering on YEAR
- filtering on Is BeforeThisMonth
In Power BI, I use a Table visualisation with Year, YYYY-MM as dimensions, New_MRR, New_MRR_AVG_Not_Working and New_MRR_AVG_Working as measures.
If I limit the page filter to a filter on YEAR (being 2024) then all measures are working as expected.
Query:
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({2024}, 'Date'[Year])
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(ROLLUPGROUP('Date'[Year], 'Date'[YYYY-MM]), "IsGrandTotalRowTotal"),
__DS0FilterTable,
"New_MRR", '_Measures'[New MRR],
"New_MRR_AVG_Not_Working", '_Measures'[New MRR AVG Not Working],
"New_MRR_AVG_Working", '_Measures'[New MRR AVG Working]
)
VAR __DS0PrimaryWindowed =
TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Date'[Year], 1, 'Date'[YYYY-MM], 1)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC, 'Date'[Year], 'Date'[YYYY-MM]
Result
| Year | YYYY-MM | IsGrandTotalRowTotal | New_MRR | New_MRR_AVG_Not_Working | New_MRR_AVG_Working |
| True | 331 | 110,333333333333 | 331 | ||
| 2024 | 24-01 | False | 96 | 96 | 96 |
| 2024 | 24-02 | False | 153 | 153 | 153 |
| 2024 | 24-03 | False | 82 | 82 | 82 |
However, when I add the filter IsBeforeThisMonth as well then one of the measures (New_MRR_AVG_Not_Working) does not work as expected.
Query
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({2024}, 'Date'[Year])
VAR __DS0FilterTable2 =
TREATAS({TRUE}, 'Date'[IsBeforeThisMonth])
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(ROLLUPGROUP('Date'[Year], 'Date'[YYYY-MM]), "IsGrandTotalRowTotal"),
__DS0FilterTable,
__DS0FilterTable2,
"New_MRR", '_Measures'[New MRR],
"New_MRR_AVG_Not_Working", '_Measures'[New MRR AVG Not Working],
"New_MRR_AVG_Working", '_Measures'[New MRR AVG Working]
)
I
VAR __DS0PrimaryWindowed =
TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Date'[Year], 1, 'Date'[YYYY-MM], 1)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC, 'Date'[Year], 'Date'[YYYY-MM]
Result
| Year | YYYY-MM | IsGrandTotalRowTotal | New_MRR | New_MRR_AVG_Not_Working | New_MRR_AVG_Working |
| True | 331 | 110,333333333333 | 331 | ||
| 2024 | 24-01 | False | 96 | 110,333333333333 | 96 |
| 2024 | 24-02 | False | 153 | 110,333333333333 | 153 |
| 2024 | 24-03 | False | 82 | 110,333333333333 | 82 |
| 2024 | 24-04 | False | 110,333333333333 | ||
| 2024 | 24-05 | False | 110,333333333333 | ||
| 2024 | 24-06 | False | 110,333333333333 | ||
| 2024 | 24-07 | False | 110,333333333333 |
In one way or another, the additional IsBeforeThisMonth filter results in the iterator AVERAGEX loses the "YYYY-MM" filter contect, I would expect that this measure (although not ideal) would work as well and that this additional filter wouldn't influence the result.
If I delete the Year dimension from the table (but keep ons using both page filters), then everything is working fine as well.
Query
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({2024}, 'Date'[Year])
VAR __DS0FilterTable2 =
TREATAS({TRUE}, 'Date'[IsBeforeThisMonth])
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('Date'[YYYY-MM], "IsGrandTotalRowTotal"),
__DS0FilterTable,
__DS0FilterTable2,
"New_MRR", '_Measures'[New MRR],
"New_MRR_AVG_Not_Working", '_Measures'[New MRR AVG Not Working],
"New_MRR_AVG_Working", '_Measures'[New MRR AVG Working]
)
VAR __DS0PrimaryWindowed =
TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Date'[YYYY-MM], 1)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC, 'Date'[YYYY-MM]
Result
| YYYY-MM | IsGrandTotalRowTotal | New_MRR | New_MRR_AVG_Not_Working | New_MRR_AVG_Working |
| True | 331 | 110,333333333333 | 331 | |
| 24-01 | False | 96 | 96 | 96 |
| 24-02 | False | 153 | 153 | 153 |
| 24-03 | False | 82 | 82 | 82 |
What is going wrong here?
I've attached my pibx file as well.
Problem AVG.pbix
Many thanks!
2 Replies
AVERAGEX(VALUES('Date'[YYYY-MM]), [New MRR]) will sum up to the month level, and after that, it will calculate the average. It seems to be working correctly to me.
AVERAGEX(VALUES('Date'[Date]), [New MRR]) sums up to the date level and then calculates the average. Check the file; it is showing the same as the month because you only have data for three dates.
Hello, thanks for your answer amitchandak .
This does work indeed for the first and third example (in my original post) but it doesn't work when I use the page filters IsBeforeThisMonth and Year AND if my table includes the year dimension as well. Question is: why is example 2 not working but 1 (another scenario where I use the Year dimension in my table and only use the IsBeforeThisMonth filter as a page filter) and 3 are? What is different causing the measure not to work in example 2?
So focussing on example 2 and 3: everything works fine without Year dimension in my table and using both page filters (IsBeforeThisMonth and Year) but after adding the Year dimension in my table the measure calculates the average over the total Year taking abstraction of the YY-MM field.
This works (last example of my orignal post):
This doesn't (second example of my original post):
Any idea? Many thanks!!
