Concatenate Multiple Columns inside Measure

[Anonymous]

Hello Guys,

I have a table, with 30 columns, the last one is a concatenation of the previous 27..
This last column, which is the concatenation of the previous ones, is used in a distinctcount measure.

Lets say I can not afford to have this last column, since processing this column in SSAS is extremely expensive, since its a column with high cardinality with 200M rows.

My idea is to drop this column, and make a measure that would make a distinct count of the concatenation of the columns..

Is it possible to do something like this? Kind of insert the concation inside of a measure??

Calculate(Distinctcount([Col1]&[Col2]&...&[colX])

Thank you!

[AlbertoFerrari · Accepted solution]

Hi Luis,

Yes, you can. That said, you should not 🙂 This code will work:

COUNTROWS ( 
    SUMMARIZE ( 
        YourTable,
        YourTable[Col1], 
        ..., 
        YourTable[Col27]
    )
)

But this code will materialize the full dataset and it is a performance (and memory) killer. I use a very similar example in the Optimizing DAX course to show what NOT to do with DAX, since it might easily result in tens of gigabytes being allocated and horrible performance.

On a very small selection of data you might obtain some results, but taking the distinct count of 200M rows is a very expensive task, you will probably need to change the model or work around it with some other technique, doing it in pure DAX is likely to be a very bad idea.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[Anonymous]

Hello Alberto!

Awesome to have a reply from such an expert!

Thank you for the reply, anyway, yes I guess the performance will be extremely bad, but with that column existing I can not process default that table on SSAS, so I am trying to find a quick get-away from this problem. Which right now is deleting that column from the model and recreate it in the only measure it is actually needed.

I will most certainly try and get around this problem some other way, but right now I cant think of anything else.