topic Report filter not working with DAX Calculation in DAX Commands and Tips

Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 11:11:18 GMT

Hi all,

I would like some help with this one please. I have the below DAX calculation and it is working fine:

TEST Custom Sales =
CALCULATE (
[Sales],
FILTER (
SalesFact,
RELATED('Date'[Financial Year]) = Max( 'Date'[Financial Year] )
)
)

The issue is when I select a value from a report filter, for example Product = "Shoes", the calculation returns (Blank)..

On the other hand when i specify a fixed value in the filter, the product filter works fine:

TEST Custom Sales =
CALCULATE (
[Sales],
FILTER (
SalesFact,
RELATED('Date'[Financial Year]) = 2020
)
)

Tables Date, Product, SalesFact are linked properly so there is no issues with the model.

Can anyone help me resolve this problem?

Re: Report filter not working with DAX Calculation

Greg_Deckler — Tue, 05 May 2020 11:07:12 GMT

It is difficult to know what is wrong with the information provided. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Is the Product column in the SalesFact table? If not, there could be a relationship issue or something going on.

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 11:10:30 GMT

Hi.

I updated the main topic.

There are no relationship issues in the model,. As per main topic the calculation seems to work fine when a fixed value is used in the calculation?

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 11:19:52 GMT

What is this measure trying to achieve?

By the way , filtering by a full (expanded) table is a very dangerous and bad idea as you will learn very soon when you start creating other measures based on this one.

Best
D

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 11:22:36 GMT

Thanks for your message.

The idea behind the calculation is to calculate total sales for financial periods. Sadly i cannot use conventional calendar calculations hence have to do it this way.

Any suggestion on how to accomplish this?

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 11:31:02 GMT

What about other products when you filter by them? Does this affect all of them?

Best
D

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 11:33:32 GMT

Yes Product filters all items in the SalesFact table in this case

The odd thing the calculation returns blank when selecting a product from a slicer, but as per example the calculation works when the Financial year is set to a fixed date ex 2020

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 11:38:30 GMT

This does the same but is following Best Practices and it's safe to use in other measures:

[Custom Sales] = var __lastVisibleFY = max( 'Date'[Financial Year] ) return CALCULATE ( [Sales], keepfilters( 'Date'[Financial Year] = __lastVisibleFY ) )

Also, for this to work you have to make sure that you do not slice by columns in the fact table, only through dimensions. What the above does is it grabs the last visible (in the current context) financial year from the Calendar and from the visible dates it only picks up the ones that are in this very last year, so [Sales] is calculated with all other filters on apart from maybe the Calendar in which only the dates from the last visible FY have been made active (within the current context, without overwriting any other filters that may exist on the Calendar).

Is this what you want? Please note that this measure does exactly what your original measure does.

Best

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 11:40:53 GMT

IF this still has issues, please return from the measure the last visible FY to see what gets calculated in the context.

Best
D

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 11:44:46 GMT

Thanks, yes agreed but still do not understand as to why the Product filter does not work with athe calculation. This is really strange behaviour.

Also do you suggest using keepfilters rather than using Filter?

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 12:23:01 GMT

I always suggest that one should learn the mechanics of DAX well 🙂 One golden rule of DAX is: NEVER FILTER A TABLE WHEN YOU CAN FILTER A COLUMN.

KEEPFILTERS should only be used when and where needed and this depends on what you are trying to achieve.

Best
D

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 12:23:53 GMT

To be able to tell you WHY it does not work, I'd need to see the model and the data.

Best
D

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 12:50:25 GMT

What is the better way to share the pbi dashboard / data model in a forum like this ?

Re: Report filter not working with DAX Calculation

Anonymous — Tue, 05 May 2020 14:58:48 GMT

If you want to share it with me only, then place the file somewhere on a shared drive and send me a link to it via a private message. Click my "avatar" (same as yours) and you'll be able to send a message.... If you want to share it publicly, post the link here.

Best
D