Forum Discussion

mmace1's avatar
mmace1
Impactful Individual
5 years ago
Solved

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

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.)

 

Need Help

5 Replies

    • mmace1's avatar
      mmace1
      Impactful Individual
      5 years ago
      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's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

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