Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

filter issue

I have the below measure which works perfectly fine but I want to make a reverse of it.
 
Epic Giveaway Units = calculate(sum('Epic Sales Data'[Net Units]),
filter('Epic Sales Data', if(and(and('Epic Sales Data'[Sale Day] >= date(2020,7,9),'Epic Sales Data'[Sale Day] <= date(2020,7,16)),'Epic Sales Data'[Offer Title]="The Escapists 2"),0,1) = 1),
filter('Epic Sales Data', if(and(and('Epic Sales Data'[Sale Day] >= date(2021,6,17),'Epic Sales Data'[Sale Day] <= date(2021,6,24)),'Epic Sales Data'[Offer Title]="Overcooked! 2"),0,1) = 1)
 
So I create the below measure which swaps around the 0 and 1 in the IF statements TRUE AND FALSE values:
 
Epic Giveaway Units = calculate(sum('Epic Sales Data'[Net Units]),
filter('Epic Sales Data', if(and(and('Epic Sales Data'[Sale Day] >= date(2020,7,9),'Epic Sales Data'[Sale Day] <= date(2020,7,16)),'Epic Sales Data'[Offer Title]="The Escapists 2"),1,0) = 1),
filter('Epic Sales Data', if(and(and('Epic Sales Data'[Sale Day] >= date(2021,6,17),'Epic Sales Data'[Sale Day] <= date(2021,6,24)),'Epic Sales Data'[Offer Title]="Overcooked! 2"),1,0) = 1)
 
This does not work however, it does if I just reverse the 0 and 1 for The Escpaists 2 but as soon as I do the same for Overcooked 2, the measure no longer works, coming up with a blank value when I set my slicer filter to either of those games. Can anyone help please?
 
 
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , try like

     

    You measure

     

    Epic Giveaway Units = calculate(sum('Epic Sales Data'[Net Units]),
    filter('Epic Sales Data', not(and(and('Epic Sales Data'[Sale Day] >= date(2020,7,9),'Epic Sales Data'[Sale Day] <= date(2020,7,16)),'Epic Sales Data'[Offer Title]="The Escapists 2"),0,1) && not(and(and('Epic Sales Data'[Sale Day] >= date(2021,6,17),'Epic Sales Data'[Sale Day] <= date(2021,6,24)),'Epic Sales Data'[Offer Title]="Overcooked! 2")) ) )

     

     

     

    reverse measure
    Epic Giveaway Units = calculate(sum('Epic Sales Data'[Net Units]),
    filter('Epic Sales Data', not(not(and(and('Epic Sales Data'[Sale Day] >= date(2020,7,9),'Epic Sales Data'[Sale Day] <= date(2020,7,16)),'Epic Sales Data'[Offer Title]="The Escapists 2"),0,1) && not(and(and('Epic Sales Data'[Sale Day] >= date(2021,6,17),'Epic Sales Data'[Sale Day] <= date(2021,6,24)),'Epic Sales Data'[Offer Title]="Overcooked! 2")) ) ))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for the reply

       

      Unfortunately that formula still didn't work and there was a syntax error