Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
@tamerj1 - sorry about the earlier post, I was a bit frazzeled when I wrote it up. Hopefully this is more clear.
I am unable to obtain the correct result from an add columns summarize pattern, when I am filtering on a dimension which does not exist within the fact table.
I am working with both forecasts and actuals, and I would like to create a version which is called "Forecast + Actuals" which is a combination of actuals and the forecast values which is after the last date of actuals.
I know you can do this via a measure like if selected value = "Forecast + Actuals" then return calculate measure, but I would prefer to do it in one measure to reduce the complexity of my measure tree.
Here is an example data set.
Date | Version | Value |
31-Oct | Forecast | 100 |
30-Sep | Forecast | 150 |
30-Sep | Actual | 120 |
31-Aug | Actual | 100 |
Here is the Version Dimension table. "Forecast + Actuals" doesn't exist in my fact table, but can be derived from the fact table through a combination of Forecast and Actuals.
Version Dimension Table |
Forecast |
Actuals |
Forecast + Actuals |
I would like a measure which would populate the following:
Version | 8/31 | 9/30 | 10/31 |
Forecast | 150 | 100 | |
Actuals | 100 | 120 | |
Forecast + Actuals | 100 | 120 | 100 |
I was trying to accomplish it like this via this measure:
DataSum =
VAR MaxActualsDate =
CALCULATE ( MAX ( FactTable[Date], Version = "Actuals" ) )
VAR SummaryData =
ADDCOLUMNS (
SUMMARIZE ( 'FactTable', 'Date'[Date], Versions[Version] ),
// this is the core table
"@SourceValue", CALCULATE ( SUM ( FactTable[Value] ) )
)
RETURN
IF (
SELECTEDVALUE ( Versions[Version] ) = "Current Forecast + Actuals",
CALCULATE ( SUMX ( SummaryData, [@SourceValue] ), [Version] = "Actuals" )
+ CALCULATE (
SUMX ( SummaryData, [@SourceValue] ),
[Version] = "Forecast",
Date[Date] > MaxActualsDate
),
SUMX ( SummaryData, [@SourceValue] )
)
I didn't find any success here - so then I decided to modify the variable table by unioning the "Forecast + Actuals" into the SummaryData variable table. When i look at this in DAX studio, the table does have "Forecast + Actuals" with values, however, I am unable to bring this into a visual.
DataSum =
var MaxActualsDate = calculate( max(FactTable[Date],Version="Actuals")
Var SummaryData = ADDCOLUMNS (
TREATAS (
UNION (
SUMMARIZE ( 'FactTable', 'Date'[Date], Versions[Version] ), // this is the core table
ADDCOLUMNS (
SUMMARIZE ( FactTable', 'Date'[Date], Versions[Version] ),
"Version", "Current Forecast + Actuals" // this is where i'm trying to add the "fake data series"
)
),
'Date'[Date],
Versions[Version]
),
"@SourceValue",
IF (
[Version] = "Current Forecast + Actuals",
CALCULATE ( SUM ( FactTable[Value] ), ALL ( Versions ), Versions[Version] = "Actuals" ),
CALCULATE ( SUM (FactTable[Value])
)
)
Return Sumx ( SummaryData, [@SourceValue])
Its something like this -
Date | Version | Currency | RWA_AIRB |
10/31 | BCL Data | USD | 100 |
10/31 | BCL Data | CAD | 100 |
10/31 | UAT | CAD | 100 |
It is still not very clear. Can we connect via teams or zoom. I'll be free after 30 min
Yeah you are right let me rework the question and post a picture of the data model
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
9 | |
9 |