Forum Discussion

YunJ's avatar
YunJ
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Wrong result when putting different date.

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 ...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
YunJ
Icon for Post Prodigy rankPost Prodigy
6 years ago

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's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      YunJ
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      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's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        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.