Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
@daxer-almighty Did you submit a bug report and if so, can you share the link here?
What is the behavior you are expecting but not seeing? It read like the country slicer going blank was what you didn't like but that behavior makes sense to me as I posted.
It's not about the slicer. It's about the table.
What about the table? I checked your file and the date slicer filters the table accordingly
BTW, you might want to check out this video by the gurus at SQLBI.com
Proud to be a Super User!
Paul on Linkedin.
The behavior of the country slicer is what I would expect when you select a quarter year that has no rows in the 'financials' table, all the options get filtered out. You could edit the interactions so the date slicer does not filter the country slicer then the list will not get cleared so you can still use it to filter the table visual.
If you take the +0 out of the [ct_rows 2] meausre then the table visul is cleared when you select Q-2 2013 also.
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
Post the but report in the Issues forum. That way if MS has any follow up questions you'll be in a position to answer them. Issues - Microsoft Power BI Community
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingmuch much appreciate it,
I really understand it now,
I highly recommand that you reach to microsoft, because you have the full understanding of this behavior.
Again thank you
@Anonymous
I replicated your tables (only the relevant fields) and don't have your issue. Everything is filtered as it should be and the measures don't make a difference (even though they shouldn't be written the way you have). You should first check if you've got the latest version of PBI Desktop and you can - if you don't mind - upload the file (with just enough data to demonstrate the issue) to a place from which we could download it and see the details with our own eyes. Otherwise, you're on your own since we can't reproduce the issue.
thank you for your reply, much appreciate it
I can ensure you that I'm running the last version of power bi desktop,
and this is the workbook, if you can try it , thank you
https://drive.google.com/file/d/1SbmtmORnLXAp3_arjDMc2zBf8RsErkrv/view?usp=sharing
@vanessafvg much appreciate it,
I tried your suggestion, but unfortunetly I'm still having the same issue, as you can see below :
result:
model:
hiya yes i see this actually more complicated.
A few questions what are you trying to do with the padding the count value with 0? and why do you want to pad it with 0? removing the padding brings back the correct data. Maybe if you can explain your business requirement, we can take a step back and do this differently, because effectively the way this is being modelled is not right for what you are wanting to do.
Proud to be a Super User!
What I'm trying to do is to turn blank in my measure into 0.
but this doesn't work when I have a date filter, it doesn't filter my dates.
and if you read @daxer-almighty answer this is a problem to do with SUMMARIZECOLUMNS.
I don't know if you have something to add on what @daxer-almighty explained.
my personal opinion is this is not a problem with summarize columns but with how you have modelled your data.
Proud to be a Super User!
Sadly, you're not right. This certainly IS a problem with SUMMARIZECOLUMNS. Why? Because the model is perfectly valid. Power BI and DAX do not complain about it, so it means people do not get any indication something could be wrong. Compare this with ambiguous models where people do get warnings or even errors when a model can't be resolved properly. On top of that, SUMMARIZECOLMNS is executed behind the scenes and nobody has any obligation to know how it works, especially business users who are not required to be DAX gurus. If a model is admissible, it should behave correctly. But a correct model does not behave correctly here. Therefore to my mind, and to any sane mind, it should be classified as a bug.
You mean kind of like expecting totals to return "correct" totals, but when they don't the problem is DAX.
Whenever I read that the result of a measure is incorrect, I JUST SMILE. (And then try to explain why the result is correct, but the OP is confused about the result)
Proud to be a Super User!
Paul on Linkedin.
And whenever I see people who try hard not to understand what the real problem is... I JUST SMILE. Too.
I am afraid many a bad model encounters issues where power bi doesn't complain. To be good at Power BI you need to know more than just the technicalities of how power bi works, you also need to understand how to model data.
Proud to be a Super User!
you will not be able to pull data from the finanial2 table, by date with this model, is there a date in finacials2? sorry i thought there was and by joining both financial tables to the date table you could filter both.
however if not you either rneed to denormalise the 2 financial tables by creating one table from them or you need to set table 3 (from diagram below) back to a bidirectional relationship. But leave the date table as is.
Think of the arrows presented here as the direction the relationships go in, in single direction one to many relationships the one filters the many. Right now the relationship of the date stops at the first financial table
change that back to birectional. but leave the date as is.
Proud to be a Super User!
I'm sorry to inform that I tried that and still didn't fix the issue, that's what I did :
and this is my new model:
I really appreciate your help, thank you.
here is the workbook if you want to try it yourself
https://drive.google.com/file/d/1SbmtmORnLXAp3_arjDMc2zBf8RsErkrv/view?usp=sharing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |