cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
data_kab
Regular Visitor

Filter and changing the measure to show 0 instead of blank in DAX

 

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:

as you can see, I filtred on 2013 and I still have rows from 2014as you can see, I filtred on 2013 and I still have rows from 2014

 

model:

Model.jpg

 

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:

for this you can see that it shows only the 2013 that I filtredfor this you can see that it shows only the 2013 that I filtred

 

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.

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

Hi again @data_kab 

 

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:

 

daxer-almighty_0-1620522456194.png

 

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

 

 

View solution in original post

21 REPLIES 21
jdbuchanan71
Super User II
Super User II

@daxer-almighty Did you submit a bug report and if so, can you share the link here?

jdbuchanan71
Super User II
Super User II

@daxer-almighty 

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.

@daxer-almighty 

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

https://youtu.be/bGVLguWf4Ls 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






jdbuchanan71
Super User II
Super User II

@daxer-almighty 

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.

jdbuchanan71_0-1620576464182.png

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.  

 

daxer-almighty
Solution Sage
Solution Sage

Hi again @data_kab 

 

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:

 

daxer-almighty_0-1620522456194.png

 

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

 

 

View solution in original post

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@daxer-almighty 

much 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 

daxer-almighty
Solution Sage
Solution Sage

@data_kab 

 

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.

@daxer-almighty 

thank you for your reply, much appreciate it 

I can ensure you that I'm running the last version of power bi desktop, 

data_kab_0-1620515316218.png

 

and this is the workbook, if you can try it , thank you 

https://drive.google.com/file/d/1SbmtmORnLXAp3_arjDMc2zBf8RsErkrv/view?usp=sharing

data_kab
Regular Visitor

@vanessafvg much appreciate it,
I tried your suggestion, but unfortunetly I'm still having the same issue, as you can see below :

result:

data_kab_0-1620486672426.png

 

model:

data_kab_1-1620486704247.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@vanessafvg 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@vanessafvg 

 

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.

@daxer-almighty 

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)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown 

 

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.   





Did I answer your question? Mark my post as a solution!

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.

vanessafvg_0-1620512345596.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@vanessafvg 

I'm sorry to inform that I tried that and still didn't fix the issue, that's what I did :

data_kab_0-1620514419058.png

and this is my new model:

data_kab_1-1620514686376.png

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors