cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Resolver II

## Unable to get values in disconnected tables

Hi

I have a client table with around 30 columns and transanctional table, a date table.

I have created a disconnected table to show the transanctions based on a bucket as below, the value will calculate transanctions based on the client table.

0,

1-5,

5-10,

10-15,

20+

Measure I had created as below

``````VAR _T1 =
SELECTCOLUMNS(
SUMMARIZE(
Client,
Client[Tier], Client[ID],
"@CallCount", [No of Trxs],
"@order", SWITCH(
TRUE(),
[No of Trxs] <=0, 1,
[No of Trxs] >0 && [No of Trxs] <=5, 2,
[No of Trxs] >5 && [No of Trxs] <=10, 3,
[No of Trxs] >10 && [No of Trxs] <=20, 4,
5
)
),
"@bkt", [@order] )
RETURN
CALCULATE (
[No of Trxs],
FILTER(
DisconnectedTable,
DisconnectedTable[ID] = _T1
)
)
``````

Don't understand why its not working.. Please guide me

1 ACCEPTED SOLUTION
Super User

@vk_pbi , Try 2 measures like

M1= SWITCH(
TRUE(),
[No of Trxs] <=0, 1,
[No of Trxs] >0 && [No of Trxs] <=5, 2,
[No of Trxs] >5 && [No of Trxs] <=10, 3,
[No of Trxs] >10 && [No of Trxs] <=20, 4,
5
)

Final Measure =
Sumx(bucket, calculate( Sumx( filter( SUMMARIZE(
Client,
Client[Tier], Client[ID]), [M1] = max(bucket[Value]) ), [No of Trxs])))

or

Final Measure =
calculate( Sumx( filter( SUMMARIZE(
Client,
Client[Tier], Client[ID]), [M1] = max(bucket[Value]) ), [No of Trxs]))

refer

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

3 REPLIES 3
Super User

@vk_pbi , Try 2 measures like

M1= SWITCH(
TRUE(),
[No of Trxs] <=0, 1,
[No of Trxs] >0 && [No of Trxs] <=5, 2,
[No of Trxs] >5 && [No of Trxs] <=10, 3,
[No of Trxs] >10 && [No of Trxs] <=20, 4,
5
)

Final Measure =
Sumx(bucket, calculate( Sumx( filter( SUMMARIZE(
Client,
Client[Tier], Client[ID]), [M1] = max(bucket[Value]) ), [No of Trxs])))

or

Final Measure =
calculate( Sumx( filter( SUMMARIZE(
Client,
Client[Tier], Client[ID]), [M1] = max(bucket[Value]) ), [No of Trxs]))

refer

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Resolver II

The above meaure works fine, but the totals are not showing properly. Any idea how to fix this ?

Resolver II

Superb, thank you 🙂

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors