Forum Discussion
DAX & Table Query Best Practice
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.
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.
3 Replies
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.
Hi Byzza
In your case, the easiest and most scalable approach is to put Budget, Actuals, Forecast, etc. into one fact table instead of keeping them separate. Structure it in a long format where each row has:
-
Date
-
Source (Actuals, Budget, Forecast)
-
Phasing (Week, Month)
-
Measure Type (Revenue, Volume, COGS)
-
Value
You can do this in Power Query by appending the tables and unpivoting the columns.
Once the data is in this format, you only need a single set of DAX measures. You can then use slicers or calculation groups (via Tabular Editor) to switch between Actuals vs Budget, Week vs Month, or even create dynamic variance calculations (Actual vs Budget, Actual vs Forecast, etc.) without hard-coding.
This way the model stays flexible, easier to maintain, and future-proof if you add new measures or data, you just add rows instead of redesigning everything.
-
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
