DAX & Table Query Best Practice

[Byzza]

Hi Brains Trust,

I've recently been setting up our Data Warehouse in MS Fabric and in the process of setting up some Semantic models for the team to use which they can create their reports from.

Scenario

One of the key requirements is basically they want to compare most combination and permutation.

So, I currently have about 10 tables similar to this (but with about another 20 value columns and a date column.)

Now depending on what they are reporting on they need to be able to compare the corresponding column in one table to another table as well as one column to another in the same table

Basicaly the tables are set to differentiate source (Budget, Actuals, Forecast, archive, and some planning ones)  and Phasing (Week and Month)


Very happy to merge these tables into one and have a column to define Source and Phasing, if this is easiest.

Question

So, the question is, what is the best way to handle this without having to create a DAX query for every single combination.

I'm happy to merge tables, unpivot tables, create mapping tables, just whatever is going to make both setup and maintenance easy.

thanks in advance for any help and suggestions.

[v-kathullac]

Hi @Byzza ,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya

[rohit1991]

Hi @Byzza ,

Given your scenario and the requirement to enable flexible comparisons across different data types (Budget, Actuals, Forecast, etc.) and phasing levels (Week, Month), the best practice would be to consolidate all your tables into a single fact table. This combined table should be structured in a long format (also known as a star schema approach), where each row includes columns like:

• Date

• Source (e.g., Actuals, Budget, Forecast)

• Phasing (e.g., Month, Week)

• Measure Type (e.g., Revenue, Volume, COGS)

• Value

This transformation can be done using Power Query (M) by appending all the tables and unpivoting the measure columns so they are in a single "Measure Type" column with their corresponding values.

With this format, you can leverage one set of DAX measures and apply dynamic filtering using slicers or calculation groups (e.g., to switch between Actuals vs Budget, or Week vs Month). This dramatically reduces maintenance overhead and avoids the need to create separate DAX measures for every possible comparison. You can also implement calculation groups in Tabular Editor to create dynamic variance calculations (e.g., Actual vs Budget, Actual vs Forecast, etc.) without needing to hard-code these into every report.

This approach offers both flexibility and scalability, supporting new data sources or measures in the future by simply adding new rows rather than restructuring your model.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[rajendraongole1 · Accepted solution]

Hi @Byzza - You're on the right track with the idea of merging tables and unpivoting. Here’s a best-practice design to simplify modeling and comparison across all sources.

In Power Query:
Import the wide table
Use “Unpivot Columns” to transform all value columns (like Volume, Revenue…) into rows under Item + Value
Add columns: Source, Phasing, and any necessary metadata.

You can write one dynamic DAX measure that works across all items (using SELECTEDVALUE or slicers)

Actual vs Budget Variance %

Variance % =
VAR Actual =
CALCULATE([Total Value], FactTable[Source] = "Actuals")
VAR Budget =
CALCULATE([Total Value], FactTable[Source] = "Budget")
RETURN
DIVIDE(Actual - Budget, Budget)

Hope the above suggestion and details helps.