Hello everyone,
when I replace a blank in a measure with 0, and having a date filter my table doesn't get filtered as shown below, for example I do filtre on 2013 but I still see rows for the other years
measures I tried :
// Try 1:
ct_rows = var _res=CALCULATE(COUNTROWS('financials (2)'),FILTER('financials (2)','financials (2)'[Product]="Carretera" ))
return _res+0
// Try 2:
ct_rows = var _res=CALCULATE(COUNTROWS('financials (2)'),FILTER('financials (2)','financials (2)'[Product]="Carretera" ))
return if(isblank(_res),0,_res)
// Try 3:
ct_rows = var _res=CALCULATE(COALESCE(COUNTROWS('financials (2)'),0),FILTER('financials (2)','financials (2)'[Product]="Carretera" ))
return _res
Result I got:
model:
in case I keep the measure with the blank, my table gets filtered by the date as shown below:
ct_rows = var _res=CALCULATE(COUNTROWS('financials (2)'),FILTER('financials (2)','financials (2)'[Product]="Carretera" ))
return _res
result for this measure:
can anyone explain to me why I got this behavior, what I did wrong and what should I do to keep the 0 and have my table filtered by date.
any help is much appreciated.
Hi again @Anonymous
I've looked at it and... do have an explanation but it gets even weirder, so please hold on tight to your seat.
First, here's the query that your visual generates:
// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('financials (2)'[Country])),
NOT('financials (2)'[Country] IN {BLANK()})
)
VAR __DS0FilterTable2 =
TREATAS({2013}, 'LocalDateTable_e211ee93-ab6a-4ce7-8478-bbd9e48b684c'[Year])
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('date'[Date], "IsGrandTotalRowTotal"),
__DS0FilterTable,
__DS0FilterTable2,
"ct_rows_2", 'financials (2)'[ct_rows 2]
)
VAR __DS0PrimaryWindowed =
TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'date'[Date], 1)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC, 'date'[Date]
The reason why you see all the years/dates is due to the fact that you use the AUTOGENERATED time hierarchy and the way SUMMARIZECOLUMNS works. First off, when you manually create columns Year and Quarter in the date table and put them in the slicer and then also on the table visual, the slicer will correctly slice data in the table visual. This is because now the columns come from one and the same table and before they came from 2 different tables. Why? Have a look at the code I got when I put the 2 columns directly into the date table:
// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('financials (2)'[Country])),
NOT('financials (2)'[Country] IN {BLANK()})
)
VAR __DS0FilterTable2 =
TREATAS({2013}, 'date'[Year])
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('date'[Date], "IsGrandTotalRowTotal"),
__DS0FilterTable,
__DS0FilterTable2,
"ct_rows_2", 'financials (2)'[ct_rows 2]
)
VAR __DS0PrimaryWindowed =
TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'date'[Date], 1)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC, 'date'[Date]
What has changed? Can you spot it?
_DS0FilterTable2 now filters a column in a different table! This, paired with how SUMMARIZECOLUMNS works, explains the behaviour you observe. The explanation of the behaviour of SUMMARIZECOLUMNS would take an article on its own, so please read this: https://www.linkedin.com/pulse/peculiar-behavior-summarizecolumns-dax-abhinav-khanduja/ where my work peer dissects the weird behavour you observe. However, this behaviour is technically correct and follows from the specification of the function.
But it gets even weirder, as I mentioned. Have a look at this:
Can you see what's wrong?
Country (which comes from financials) gets filtered in the slicer because the Country slicer executes this DAX:
/ DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(SUMMARIZE(VALUES('date'), 'date'[Year], 'date'[Quarter])),
AND(
'date'[Year] IN {"2013"},
NOT(('date'[Year], 'date'[Quarter]) IN {("2013", "Q-1")})
)
)
VAR __DS0Core =
CALCULATETABLE(
VALUES('financials'[Country]),
KEEPFILTERS(__DS0FilterTable)
)
VAR __DS0PrimaryWindowed =
TOPN(101, __DS0Core, 'financials'[Country], 1)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
'financials'[Country]
but the countries are not filtered in the table visual. And now you don't even get a chance to click anything in the slicer to limit the entries in the table!
"Cool," isn't it? 🙂
As I said, this behaviour according to all common sense is absurd and completely wrong but technically correct from the point of view of SUMMARIZECOLUMNS.
I think Microsoft have a problem. This issue needs to be reported to them. I would classify this behaviour as a bug and this is a bug of a very big callibre.
I'll have to make sure this reaches Microsoft's attention since such a behaviour of SUMMARIZECOLUMNS and the auto-generated date table is not acceptable by any means.
@Greg_Deckler, @edhans, @amitchandak, @OwenAuger, @Fowmy, @jdbuchanan71, @HotChilli... Guys, can you please help file this bug properly so that it gets Microsoft's attention?
Many thanks.
Here is a link to the file that demonstrates the above: https://1drv.ms/u/s!ApyQEauTSLtOgZZvc-RUdoyLZZoQgQ?e=64tk79
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
