Bucketing in DAX and zero values handling

[pgolbi]

Dear DAX gurus,

I am new to this wonderful world and ran into strange and possibly trivial issue. Variance By Group for all rows where 'Train'[Variance In Seconds]=0 are placed in "Not Set" group. It works perfect for all other rows.

Calculated column formula is below:

Variance By Group= 

IF (
    'Train'[Variance In Seconds] = BLANK (),
    "Not Set",
    CALCULATE (
        VALUES ( 'Period Groups'[Group Desc] ),
        FILTER (
            'Period Groups',
            'Train'[Variance In Seconds]
                >= 'Period Groups'[Period Start]*60
                && 'Train'[Variance In Seconds]
                < 'Period Groups'[Period End]*60
        )
    )
)

Period Groups looks like:

 

Thank you! 

[Mansi1]

Hi,

I am new to this forum and I am new to power bi.

I have couple of questions but i don't know how to post it to forum so I am doing it this way.

I want to create new table for setting up range like this 100-120,120-140,140-160.

I want to use this range into chart. I have reffrence column. I figure out this Interval = 20
NLabelMin = bottom of interval range that contains TotalNlbsAc
NLabelMax = top of interval range that contains TotalNlbsAc
NLabel = text(NLabelMin) & "-" &text(NLabelMax)

But i don't know how to create bottom of interval range or top of interval range.

Just wondering if anyone can help.

 This is the refrence picture. I want to create something like this.

Thanks in advance.

 Regards,

Mansi.

[Eric_Zhang]

@pgolbi

The DAX fomula looks good. I'd doubt it is some data issue in your scenario that lead to the unexpected "Not Set" instead of "Between 0 and 15". Is it possible to share any sample data, even better the pbix file, to reproduce the issue in your case? If possible, please do mask sensitive data before sharing. You could upload the file/sample data in some network drive(Onedrive, Google drive etc) and share the link. If you don't want it public, you can send the link in a private message.

[pgolbi · Accepted solution]

Hi Eric,

I found the issue.

Instead of

'Train'[Variance In Seconds] = BLANK ()

should be

ISBLANK('Train'[Variance In Seconds])

as

0 = BLANK() returns TRUE. Strange but must be a reason behind.

Thank you for you response.