Design question fact table rows vs columns

[Svet_Dimov]

I have a question regarding what would be a more optimal design of a directquery fact table. Currently I have a table in the following format:

• timestamp
• dimension
• dimension_A_value (Null for all rows where dimension is not A)
• dimension_B_value (Null for all rows where dimension is not B)
• dimension_C_value (Null for all rows where dimension is not C)
• ...
• dimension_Z_value (Null for all rows where dimension is not Z)

In my visualizations I am using only one or two different dimensions with simple aggregation measures (SUM or MAX).

I was wondering if having the design of table changed to the one below would be more performant:

• timestamp
• dimension
• value

For the visualizations I would use CALCULATE(SUM(value), dimension = "A") instead of SUM(dimension_A_value).

My gut feeling is that it would perform better, but I am new to using DirectQuery connections and I would appreciate the community's input.

[Svet_Dimov]

Thanks a lot for the response, @Greg_Deckler! My main concern is that  I would be adding a CALCULATE function to the DAX and I am not sure if it has a performance Impact on the DirectQuery.

[v-yajiewan-msft]

Hi @Svet_Dimov ,Greg_Deckler, thank you for your prompt reply!

The short answer is Yes, quoted from the official documentation:

At least initially, it's recommended to limit measures to simple aggregates.

The aggregate functions include SUM, COUNT, MIN, MAX, and AVERAGE.

Then, if the measures are sufficiently responsive, you can experiment with more complex measures, but paying attention to the performance for each.

While the Calculate DAX function can be used to produce sophisticated measure expressions that manipulate filter context, they can generate expensive native queries that don't perform well.

Best regards,

Joyce

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

[Greg_Deckler]

@Svet_Dimov I agree with you that the second format seems much better suited to how Power BI wants to see its data and *should* perform better and certainly shouldn't perform worse.