Forum Discussion

tracyhopaulson's avatar
tracyhopaulson
Resolver I
5 years ago
Solved

How to dynamically group values

I've grouped a list below but each time the report is refreshed, the new data get thrown into the "other" bucket and I have to manually group them.  Is there another option to setup dynamic grouping ...
Need Help
  • negi007's avatar
    negi007
    5 years ago

    tracyhopaulson You can do one thing, replace blank values in your data with a -1. You can make changes in the powerquery window like below and then change your dax as well. Performing calculation with blank values will not be possible, it is advisible to have numeric values in the data

     

     

    Grouping =
    Var day_chg = IF(ISBLANK('Order'[#daysChange]),-1,'Order'[#daysChange])
    return
    if(day_chg = -1,"Forecast",
    if(day_chg=0,"On time",
    if(AND(day_chg>20,day_chg<40), "Late",
    if(AND(day_chg>40, day_chg<100), "Very Late","Others"
    ))))

     


     
  • tracyhopaulson's avatar
    tracyhopaulson
    5 years ago

    negi007  thanks again for your help! Yes this is exactly what I need. 

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

tracyhopaulson Not sure about the issue you are having with the grouping/binning, but you could always just create a measure, like:

Measure = 
  VAR __DateGrouping = MAX([DateGrouping])
RETURN
  SWITCH(TRUE(),
    __DateGrouping = "Late","1-99 days",
    __DateGrouping = "On Time","0 days",
    __DateGrouping = "Very Late","100+ days",
    __DateGrouping = "Very Early","Future",
    "Other"
  )