Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm trying to SUM a column in table1 but if the value is (blank) then I want to use the value from table1 based on date, prefix (the other columns). There is no direct relationship between the two tables but are proxy linked through prefix and date tables
Prefix and Date table are pulled in from an external source and then linked so it is possible to slice/filter mutliple tables.
I feel this should be easily do-able but must be missing a trick with relationships.
Any suggestions will be welcomed
Many thanks,
Oliver
Solved! Go to Solution.
Hi @owallis ,
How about create a "Value 2" column in "Forecast" table?
Value 2 =
VAR PlanValue =
LOOKUPVALUE (
Plan[Value],
Plan[Date], Forecast[Date],
Plan[Prefix], Forecast[Prefix]
)
RETURN
IF ( ISBLANK ( Forecast[Value] ), PlanValue, Forecast[Value] )
Or, modified @mahoneypat 's measure like so:
NewMeasure =
VAR __planvalue =
SUM ( Plan[Value] )
VAR __forecastvalue =
CALCULATE (
SUM ( Forecast[Value] ),
TREATAS ( VALUES ( Plan[Date] ), Forecast[Date] ),
TREATAS ( VALUES ( Plan[Prefix] ), Forecast[Prefix] )
)
RETURN
IF ( ISBLANK ( __forecastvalue ), __planvalue, __forecastvalue )
Measure 2 =
IF (
HASONEVALUE ( 'Date'[Date] ),
[NewMeasure],
SUMX ( 'Forecast', [NewMeasure] )
)
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @owallis ,
How about create a "Value 2" column in "Forecast" table?
Value 2 =
VAR PlanValue =
LOOKUPVALUE (
Plan[Value],
Plan[Date], Forecast[Date],
Plan[Prefix], Forecast[Prefix]
)
RETURN
IF ( ISBLANK ( Forecast[Value] ), PlanValue, Forecast[Value] )
Or, modified @mahoneypat 's measure like so:
NewMeasure =
VAR __planvalue =
SUM ( Plan[Value] )
VAR __forecastvalue =
CALCULATE (
SUM ( Forecast[Value] ),
TREATAS ( VALUES ( Plan[Date] ), Forecast[Date] ),
TREATAS ( VALUES ( Plan[Prefix] ), Forecast[Prefix] )
)
RETURN
IF ( ISBLANK ( __forecastvalue ), __planvalue, __forecastvalue )
Measure 2 =
IF (
HASONEVALUE ( 'Date'[Date] ),
[NewMeasure],
SUMX ( 'Forecast', [NewMeasure] )
)
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Not exactly sure what you are trying to accomplish but suspect you can get there with an approach like this.
NewMeasure =
VAR __planvalue =
SUM ( Plan[Value] )
VAR __forecastvalue =
CALCULATE (
SUM ( Forecast[Value] ),
TREATAS ( VALUES ( Plan[Date] ), Forecast[Date] ),
TREATAS ( VALUES ( Plan[Prefix] ), Forecast[Prefix] )
)
RETURN
IF ( ISBLANK ( __planvalue ), __forecastvalue, __planvalue )
I would also suggest you simplify your model if possible. You can likely combine your Forecast and Actuals tables. Simple model, simple DAX.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
76 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |