Calculated Column vs Measure Performance in SQL Imported Source

[Anonymous]

Hi everybody,

I'm currently wondering which of the following ways would be more efficient cause I have a very large dataset and want to be as effective as possible.

I have the following data structure (example)0:

Name_Of_Product	Color	Status	Category
A	Green	Used	Hifi
B	Green	New	Car
C	Blue	Used	Car

Now I want to have a visual that shows count of all unique (color,status,category) products.

523 Green Used Hifi Products

245 Green New Car Products

I can accomblish this in two ways:

1: Calculated Column

I can create a concatenated string of the columns and use this string as legend for my visual, works fine, is easy but seems quite resource intensive for me

2: Measures

I can create a measure for every combination possible (in my case thats not more then 12) and use each measure as value. For me this seems to be more efficient, it will not work if I dont know the combinations before but in this case I know them. Another problem might be that cross filter will not work when I use the measures afaik.

What do you think is the better way?

We talk about 7.5 Million datasets (per year actually, but starting with one first..)

Thankys

[V-lianl-msft]

Hi @Anonymous ,

Power Bi is not an appropriate tool for data processing. Improving the performance of calculation formulas may not be effective for such a largedata set.
Maybe you can try to learn about modeling with SSAS, using powerbi as a visualization tool.
Or use incremental refresh in power bi.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Please note is it a trade-off most of time, what I understand. As the column gets calculated after data load, so load time will increase. if there is a complex calculation it will increase measure time.  Try to avoid Sumx, Minx etc in Mesure unless most needed.

Refer

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/