Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX formula issue

I have 5 DAX conditions and an IF condtion to return the measure result. The IF comparison condition is returning the result of only the month selected in Month slicer instead of returing last 3 months data when IF condition is True. The individual DAX conditions return correct result. The issue is in IF statement only. Can someone help?

 

BillDoc=
var Orders_1= CALCULATE(COUNTROWS(FILTER(SUMMARIZE('V2 Secondary Sales Fact','V2 Secondary Sales Fact'[Outlet ID],"Orders",CALCULATE(DISTINCTCOUNT('V2 Secondary Sales Fact'[Bill Document Number]))),[Orders]=1)))

 

var Orders_2= CALCULATE(COUNTROWS(FILTER(SUMMARIZE('V2 Secondary Sales Fact','V2 Secondary Sales Fact'[Outlet ID],"Orders",CALCULATE(DISTINCTCOUNT('V2 Secondary Sales Fact'[Bill Document Number]))),[Orders]=1)),DATEADD('Date'[FullDate], -1, MONTH))

 

var Orders_3= CALCULATE(COUNTROWS(FILTER(SUMMARIZE('V2 Secondary Sales Fact','V2 Secondary Sales Fact'[Outlet ID],"Orders",CALCULATE(DISTINCTCOUNT('V2 Secondary Sales Fact'[Bill Document Number]))),[Orders]=1)),DATEADD('Date'[FullDate], -2, MONTH))

 

 var Orders_4= CALCULATE(COUNTROWS(FILTER(SUMMARIZE('V2 Secondary Sales Fact','V2 Secondary Sales Fact'[Outlet ID],"Orders",CALCULATE(DISTINCTCOUNT('V2 Secondary Sales Fact'[Bill Document Number]))),[Orders]=1)),DATEADD('Date'[FullDate], -3, MONTH))

 

var orders_3M=CALCULATE(COUNTROWS(FILTER(SUMMARIZE('V2 Secondary Sales Fact','V2 Secondary Sales Fact'[Outlet ID],"Orders",CALCULATE(DISTINCTCOUNT('V2 Secondary Sales Fact'[Bill Document Number]))),[Orders]=1)), DATESINPERIOD('Date'[FullDate],MAX('Date'[FullDate]),-3,MONTH))

 

return
if(((Orders_1>=Orders_2)&&(Orders_2>=Orders_3)&&(Orders_3>=Orders_4)),orders_3M, BLANK())

Need Help

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    5 years ago
    are you able to provide some data? so basically what you saying is you want your measure to ignore the slicer? how are you using this measure?
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      The variable orders_3M in  IF condition- if(((Orders_1>=Orders_2)&&(Orders_2>=Orders_3)&&(Orders_3>=Orders_4)),orders_3M, BLANK()) is not returning last 3 months data based on month slicer selection.

       

      For ex- For month selection Apr, it returns data of Feb and Apr instead of Feb, Mar, Apr. 

      (Orders_1>=Orders_2)&&(Orders_2>=Orders_3)&&(Orders_3>=Orders_4) condition holds TRUE.

       

      I am using this measure in a Column chart with Values as the 'measure name' and Axis as 'month'.

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        5 years ago
        to be honest its hard to see whats going on without having some data to look at, have you checked each measure to see what its returning?
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Could you provide me with your data model and show me the screenshot of your issue?

    Or you can provide me with your pbix file by your Onedrive for Business. You can tell me your calculate logic in you measure.

    This may make it easier for me to understand what result you want.

     

    Best Regards,

    Rico Zhou

     

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