Forum Discussion
Report filter not working with DAX Calculation
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?
13 Replies
- Greg_Deckler
Community Champion
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.
- AnonymousNot applicable
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?
- AnonymousNot applicableWhat 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- AnonymousNot applicable
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?
- AnonymousNot applicableWhat about other products when you filter by them? Does this affect all of them?
Best
D