Bucket values, when you can't add a Calculated Column

[mmace1]

I have a report that I've added a calculated column to, in order to bucket values. 

PmtSizeBracket = 
SWITCH (
TRUE(),
'Payment (MC Only)'[Amount] < 100, "Under $100",
'Payment (MC Only)'[Amount] < 500, "$100-$499",
'Payment (MC Only)'[Amount] < 1000, "$500-$999",
'Payment (MC Only)'[Amount] < 1500, "$1000-$1499",
'Payment (MC Only)'[Amount] < 2000, "$1500-$1999",
'Payment (MC Only)'[Amount] < 5000, "$2000-$4999",
"Over $5k"
)

However, I'm rebuilding this report as a Live Connection - so I can't add a calculated column to the Fact table.  

How can I still bucket things? 

(I do have the preview feature enabeled, such that I can add new tables alongside my live connection, if that helps. 

I thought of creating a new table of every distinct [Amount], then adding a calculated column to that / connecting the tables - but the # of distinct values exceeds the limit of 1M rows.)

[amitchandak · Accepted solution]

@mmace1 , Bucketing using a measure you need an independent table, Not sure you can get that.

Please check my example bucket on measure : https://www.youtube.com/watch?v=CuczXPj0N-k

[mmace1]

---

@amitchandak wrote:

@mmace1, Bucketing using a measure you need an independent table, Not sure you can get that.

---

Needing an independent table was my conclusion as well, and no - I can't, since the # of distinct values is too high to reference.  So the alternative is to bring in an entirely seperate query as a table...

And at this point I'll just write seperate measures for every bucket, and use that!

Thanks, just wanted to be sure I wasn't missing some obvious way. 

[Ashish_Mathur]

Hi,

One should be able to solve this with a single measure (using a disconnected table).  Share some raw data to work with.

[mmace1]

Sure, thanks. 

Example file in Dropbox 

I tried doing a SUMMARIZECOLUMNS, but got a 'results of a query to external data sources has exceeded the maximum allowed size of 1M rows'.  I had a CALCULATE/FILTER on the SUMMARIZECOLUMNS, to only get the unique values I needed (about 30k), but same message.  

[Ashish_Mathur]

Hi,

While i am certain, i can solve this with a single measure, on 1 million rows even my solution will not work.