Report filter not working with DAX Calculation

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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?

[Anonymous]

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

Best
D

[Anonymous]

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

D

[Anonymous]

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?

[Anonymous]

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

Best
D

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

Best
D

[Anonymous]

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

[Greg_Deckler]

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.

[Anonymous]

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?