CONCAT in a measure which should behave like a column

[Anonymous]

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.

[Sahir_Maharaj]

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.

[Anonymous]

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.