topic Re: CONCAT in a measure which should behave like a column in DAX Commands and Tips

CONCAT in a measure which should behave like a column

Anonymous — Fri, 05 Jul 2024 14:06:04 GMT

Hi all,

I have a transactionID that I have split up in two parts for memory reasons (very high cardinality). See here: Optimizing High Cardinality Columns in VertiPaq - SQLBI

Now in Desktop I am trying to bring these two parts together again in a measure (not in a calculated measure as that would use memory again). According to the example this is possible with (although I have used SELECTEDVALUE instead of VALUES):

VALUES( Fact[TransactionHighID] ) * 10000 + VALUES( Fact[TransactionLowID] )

However, it does not behave as I would like it to be. What I want if I create a table visual is that I get a row for every unique value of the TransactionID.

E.g.:

Color	TransactionID
Blue	13345678
Blue	12845679
Yellow	16345666
Yellow	17895699

But what I get is an empty table visual.

Re: CONCAT in a measure which should behave like a column

Sahir_Maharaj — Sun, 07 Jul 2024 13:28:15 GMT

Hello Anonymous,

Can you please try creating a new table in Power BI to help achieve this:

UniqueTransactionTable = SUMMARIZE( 'Fact', 'Fact'[Color], 'Fact'[TransactionHighID], 'Fact'[TransactionLowID], "TransactionID", 'Fact'[TransactionHighID] * 10000 + 'Fact'[TransactionLowID] )

Let me know if you might require my further input.

Re: CONCAT in a measure which should behave like a column

Anonymous — Mon, 08 Jul 2024 11:45:12 GMT

Thank you for your reply, Sahir_Maharaj .

Unfortunately creating a new table with SUMMARIZE saves the new table in memory again, so this does not solve my primary problem of having a semantic model that is too big (in GB) due to the high cardinality of the transactionID column.