Dynamic buckets based on slicer

[radzyck]

Hi guys,

I have this table

usr box app device
u1  b1  a1  d1
u1  b1  a1  d2
u1  b1  a2  d3
u1  b2  a2  d4
u2  b1  a1  d5
u2  b2  a3  d6
u2  b2  a4  d7
u2  b3  a1  d8
u3  b3  a1  d9
u3  b4  a1  d10
u3  b3  a2  d11

I've created 2 slicers on column box and app. In a Clustered column chart, on values I have count distinct usr. On legend I want to calculate a breakdown on usr by device. The buckets are 1 device, 2 devices, 3 devices, more that 3 devices.

if no selections in slicers:

• count distinct usr = 3
• 2 usr -> more that 3 devices
• 1 usr -> 3 devices

if b1 is used as filter:

• count distinct usr = 2
• 1 usr -> 3 devices
• 1 usr -> 1 device

if a1 is used as filter:

• count distinct usr = 3
• 3 usr -> 2 devices

if a1 and b1 are used as filters:

• count distinct usr = 2
• 1 usr -> 2 devices
• 1 usr -> 1 device

How can I do this?

[radzyck]

Hi guys,

I have this table

usr box app device
u1  b1  a1  d1
u1  b1  a1  d2
u1  b1  a2  d3
u1  b2  a2  d4
u2  b1  a1  d5
u2  b2  a3  d6
u2  b2  a4  d7
u2  b3  a1  d8
u3  b3  a1  d9
u3  b4  a1  d10
u3  b3  a2  d11

I've created 2 slicers on column box and app. In a Clustered column chart, on values I have count distinct usr. On legend I want to calculate a breakdown on usr by device. The buckets are 1 device, 2 devices, 3 devices, more that 3 devices.

if no selections in slicers:

• count distinct usr = 3
• 2 usr -> more that 3 devices
• 1 usr -> 3 devices

if b1 is used as filter:

• count distinct usr = 2
• 1 usr -> 3 devices
• 1 usr -> 1 device

if a1 is used as filter:

• count distinct usr = 3
• 3 usr -> 2 devices

if a1 and b1 are used as filters:

• count distinct usr = 2
• 1 usr -> 2 devices
• 1 usr -> 1 device

How can I do this?

[Greg_Deckler]

I'm pretty sure that this is a case of using a measure coupled with the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...