topic Countrows of summarize ignores slicer in DAX Commands and Tips

Countrows of summarize ignores slicer

juan_pablo — Tue, 14 Apr 2020 18:50:49 GMT

Hi,
Why in the following measure the slicer on the "ATA" field is ignored and 2018 dates appear even they are not selected in the slicer? (see imagen below. "|" is just my list separator ",")

The table is not affected by any other slicer.

Re: Countrows of summarize ignores slicer

Greg_Deckler — Tue, 14 Apr 2020 22:31:54 GMT

Because the FILTER clause in CALCULATETABLE overrides any context filters for columns specified within the filter clause. Replace your CALCULATETABLE with FILTER. FILTER will keep your current context and then filter down from that to remove the blanks.

Re: Countrows of summarize ignores slicer

juan_pablo — Tue, 14 Apr 2020 23:26:45 GMT

Hi Greg_Deckler thank you for your reply, but why is the measure evaluated in 2018 dates? shouldn't the slicer filter the rows in the table too and consider only 2019 months?

For example, in this new measure, 2018 still is used to evaluate the measure even I'm not repleasing the "ATA" date column in the filter argument of CALCULATETABLE, why?

Re: Countrows of summarize ignores slicer

Greg_Deckler — Wed, 15 Apr 2020 00:41:36 GMT

Because your slicer is using the column ATA and your CALCULATETABLE's filter clause ALSO uses the column ATA. Thus, the filter in the CALCULATETABLE's filter clause REPLACES the context filter on the slicer. This is just how CALCULATETABLE works. It is clearly spelled out in the documentation:

https://docs.microsoft.com/en-us/dax/calculatetable-function-dax

Remarks

The CALCULATETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. For each column used in a filter argument, any existing filters on that column are removed, and the filter used in the filter argument is applied instead.

This is what I explained before. I am not sure how to explain this any other way.

Re: Countrows of summarize ignores slicer

Anonymous — Wed, 15 Apr 2020 12:02:31 GMT

Probably you are taking dates from different tables. take them from the same table and it'll work.

Here's how to create good and predictable models:

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA

Best
D

Re: Countrows of summarize ignores slicer

Anonymous — Wed, 15 Apr 2020 12:04:29 GMT

By the way. You should never use the automatic date hierarchies created in PBI. You must/should use your own calendar if you want to keep your sanity.

Best
D

Re: Countrows of summarize ignores slicer

juan_pablo — Wed, 15 Apr 2020 13:31:21 GMT

Calm down Greg_Deckler I'm just asking and learning.

The issue with the measure and the CALCULATETABLE behavior is understood, my question now is, why the measure is being evaluated on 2018 dates? Whatever the measure is, it shouldn't be evaluated on 2018 dates (rows of the table visual) because the slicer should be hiding those rows on the visual, no?

Or the slicers don’t affect row and column headers and just affect measures inside the tables?

Re: Countrows of summarize ignores slicer

juan_pablo — Wed, 15 Apr 2020 13:33:02 GMT

It's the same table and column, still, thank your for the reply and the videos.

Re: Countrows of summarize ignores slicer

juan_pablo — Wed, 15 Apr 2020 13:46:44 GMT

Anonymous

Yes, that's the main recommendation they give in literature, but that approach only works when you have few dates in the model. In my case, almost all my data are dates, tables full of dates, so creating a date table per date column or dozens of inactive relationships makes the model confusing, the DAX complex and the visualization task time consuming. I really find useful the auto hierarchies Power BI creates automatically in each date.

If you know how to handle these scenarios where dates are the main data type on the model and still use a date table, would be very helpful if you could share with me the links or sources to learn about it.

Thank you!

Re: Countrows of summarize ignores slicer

Greg_Deckler — Wed, 15 Apr 2020 14:00:08 GMT

juan_pablo - I can't recreate it so it is difficult to know what is going wrong on your end. See attached PBIX file.

Re: Countrows of summarize ignores slicer

Anonymous — Wed, 15 Apr 2020 14:10:17 GMT

Truth be told, it could be that the auto-hierarchies are the real culprit. It's easy to check: create at least one proper calendar and connect to the column with the dates and slice by the calendar (hiding the column in the fact table). If it works OK, then you know where the issue is...

Best
D

Re: Countrows of summarize ignores slicer

juan_pablo — Fri, 24 Apr 2020 16:59:22 GMT

Hi everyone! specialiy Greg_Deckler

I found the explanation to what is happening and has to do with the behavior of the custom visual.

The behavior is not wrog, is just unexpected.

The custom visual "Timeline 2.1.1" (the one I'm using to filter the table) picks the date field and not the autohierarchy as built-in slicers do.

Take a look at the two imagens below which explain easily what is happening.

The Power BI slicer automatically picks the autohierarchy and the custom visual does not:

Because the table visual is using the autohierarchy as row headers, the custom visual filter doesn't affects the table visual, only the measure.

Thank you Greg_Deckler for your file that guided me to make tests with the custom visual.

Re: Countrows of summarize ignores slicer

Greg_Deckler — Fri, 24 Apr 2020 19:37:47 GMT

Man, glad that one is solved, that was bugging me that I couldn't recreate it!!