Unable to get values in disconnected tables

[vk_pbi]

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

[amitchandak · Accepted solution]

@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

[vk_pbi]

Hi @amitchandak 

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

[vk_pbi]

@amitchandak 

Superb, thank you 🙂