Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need HELP with DAX please!

Hello All   I have a complex request to be able to select a sample of data for auditing based on user-selected dates.   What I want to be able to do is:   Take the Date diff between the User-s...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    This can be a little difficult if your model involves slicers. 
    You are using a calculated column instead of a measure, which will cause the result to be static(not recalculated based on your current filter context). If you use measure instead, the result will be correct, but you won't be able to apply it to the 'Axis' on the bar chart.

     

    Best Regards,
    Community Support Team _ Eason

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak thank you for your quick response. So your suggestion works fine in Measure. Is it possible to Group the Measure week into a range? For example say if the Measure week is returning 0.43, then Weeks Range as 0-1 Weeks?

 

I need this in a column so that I can create a clustered Chart, total customers by Week Range - something like below.

 

 

 

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hi Anonymous ,

     

    I have a first draft here:

     

    The question is though what to do when it exactly hits a week (or a multiple of that, see 7 in picture). Should it then show 6-7 Weeks or 7-8 Weeks? You might be able to tweak the DAX below accordingly, but this might be a start for you:

    TomsGroupingWeekTable = 
ROUNDDOWN( Table[In Weeks], 0 ) & "-" & ROUNDUP( Table[In Weeks], 0 ) & " Weeks"

     

    Let me know if you need further assistance!

    /Tom

    https://www.tackytech.blog

    https://www.instagram.com/tackytechtom

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      tackytechtom Well actually, I added a IF statement to deal with the -ve In Weeks figures but it didnt go well for other In Weeks figures. 

       

      You can see below, User selected date is 31/07/2021, based on that you can see there should be a range from 0-1 Weeks, 1-2 Weeks, and 2-3 Weeks etc.. but its shows as 0-1 Weeks for all. 

       

       

      This is my Dax code

       

       

      tackytechtom, not sure if I am missing something obvious, any help would be much appreciated. 

       

      Many thanks

       

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, Anonymous 

        This can be a little difficult if your model involves slicers. 
        You are using a calculated column instead of a measure, which will cause the result to be static(not recalculated based on your current filter context). If you use measure instead, the result will be correct, but you won't be able to apply it to the 'Axis' on the bar chart.

         

        Best Regards,
        Community Support Team _ Eason