cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pgolbi Frequent Visitor
Frequent Visitor

Bucketing in DAX and zero values handling

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:

 

 PeriodGroups.jpg

 

Thank you! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
pgolbi Frequent Visitor
Frequent Visitor

Re: Bucketing in DAX and zero values handling

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.

 

 

3 REPLIES 3
Moderator Eric_Zhang
Moderator

Re: Bucketing in DAX and zero values handling

@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 Frequent Visitor
Frequent Visitor

Re: Bucketing in DAX and zero values handling

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.

 

 

Mansi1 Visitor
Visitor

Re: Bucketing in DAX and zero values handling

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.

 

 

Picture.png

 

Thanks in advance.

 Regards,

Mansi.