Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

DAX help with data buckets

I have a Power BI report that is sourced from an SSAS model.

 

I have an alert table that is at the device level and has a record for each alert from a device.

I have these measures:

A count of the average number of devices that send an alert in the given date range (defined by a date slicer in the report)

An average percentage of the number of devices that send an alert in the given date range

Percent buckets (a switch statement to assign a bucket to the above measure, 0-25, 25-50, 50-75, 75-100)

 

I have a table that holds the device informaton which has an inactive bi-directional relationship with the alert table.

My final table is a mapping of device to customer which has a bi-directional relationship to the device table.

 

What I would like to do is have a chart/table that shows the number of customers that fall into each percentage bucket.  For example, customer A had 35% of its devices send an alert in the given date range, so it would be in the 25-50 bucket.

 

I have a measure for the customer count which does a distinct count of customers from the customer table, and I use the inactive relationship between the alert and device tables.  This gets me the total count that I am looking for.

 

If I create a table with the percentage bucket, customer number and customer count the data is displaying correctly.  Obviously, with the customer number in there the customer count is 1 for every record.  When I remove the customer count the table shows only the lowest percent bucket, 0-25, and shows the total count for the customer count measure.  It does not "group" the accounts by the percentage buckets.

 

Any help on this is greatly appreciated.

1 Reply