Forum Discussion

eburke's avatar
eburke
Helper II
8 years ago
Solved

banding not working

Hi all,

 

I've started using Banding for the first time, a fairly simple one spliting 24 hours into 4 bands, Table is set up as per below:

 

 

This has been used in a calculated column to assign a band to a row based on a time column = 

 

Time Bands = CALCULATE(VALUES(Band[Band]),FILTER(Band, 'Actual Services'[Activity Start Time]>=Band[Low]&&'Actual Services'[Activity Start Time]<Band[High]))

 

No matter what I do there is always one set of data that comes up with a blank band, currently its anything that should fall into the band category 6pm - Midnight.

 

Any idea how to fix this?  Thanks,

Need Help

5 Replies

  • BILASolution's avatar
    BILASolution
    Solution Specialist
    8 years ago

    Hi eburke

     

    Try this Calculated Column...

     

    Time Bands = var actualtime = FIRSTNONBLANK('Actual Services'[Activity Start Time];1)
             var bandd = FIRSTNONBLANK(SELECTCOLUMNS(CALCULATETABLE(Band;Band[Low]<=actualtime;Band[High]>=actualtime);"Band";Band[Band]);1)
             var midnight = LOOKUPVALUE(Band[Band];Band[High];TIME(24;0;0))              
return

IF(ISBLANK(bandd);midnight;bandd)

    Regards

    BILAsolution

    • eburke's avatar
      eburke
      Helper II
      8 years ago

      Hi, thanks for this, it comes up with an error The syntax for ';' is incorrect.