Wrong result when putting different date.

[YunJ]

Hi,

I wrote a measure like following:

When I put "Start Date" in the table, the result is correct. But for the bottom two tables, which I put "YearMonth" and "YearWeek" in the table, the result is not correct. Any solution to fix this?

Thanks a lot for your help!

[Icey · Accepted solution]

Hi @YunJ ,

To my understanding, try to create your measure like so:

8 (2) =
CALCULATE (
    SUM ( timetest[Sales] ),
    FILTER (
        ALLSELECTED ( timetest ),
        timetest[Land Date] IN VALUES ( timetest[Start Date] )
            && timetest[Presale_or not] = "Y"
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[YunJ]

Hi @Icey 

Thanks for your great solution.

May I ask why you use ALLSELECTED function? and why you use 

timetest[Land Date] IN VALUES ( timetest[Start Date] )

rather than  

timetest[Land Date] = SELECTEDVALUE( timetest[Start Date] )

Thanks for your time and patience!

Yun 

[Icey]

Hi @YunJ ,

1. I use ALLSELECTED() to Remove context filters from columns and rows in the current query, while retaining all other context filters or explicit filters. For details, please refer to this blog: The definitive guide to ALLSELECTED.

2. If use SELECTEDVALUE(), the expression only works when one record "selected". But use VALUES(), it will return a list of records.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[YunJ]

Thanks a lot @Icey ! I read the link and some source about ALLSELECTED. But I'm still wondering why we need to remove context filters from columns and rows? 

For every row it has a unique value right? So it doesn't ignore the row filters right?

(Sorry I'm new to power bi, and sorry if I make my question silly... just still confused about ALL family DAX although learn for some time..)

[Icey]

Hi @YunJ ,

ALLSELECTED( timetest ) removes all filters of timetest table, except those included on this report page.

And the expression below is the reason why [Start Date] and [Year Week] keep working as filters. You can try to delete it and the result will be different.

timetest[Land Date] IN VALUES ( timetest[Start Date] )

This may be a little difficult to understand. It is suggested to refer to some documents and videos.

document:

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT

video:

The Main Difference Between ALL And ALLSELECTED - Power BI & DAX

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[YunJ]

Thanks for your sharing @Icey 

[parry2k]

@YunJ why you are using allexcept function? Can you define what you are trying to achieve?

[YunJ]

Hi @parry2k 

I wrote allexcept(table[category]) to make my Category slicer can work. When I only used all(table), the Category slicer does not work.

Thanks

Yun