Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have 2 tables and 2 columns and with this I need to create a formula based on 4 different scenarios:
Logic (scenarios):
IF Forecast[Name] IS NOT IN Actual[Name] THEN "Removed"
IF Actual[Name] IS NOT IN Forecast[Name] THEN "New"
IF Actual[Name] IS EQUAL TO Forecast[Name] THEN IF Actual[Level] IS EQUAL TO Forecast[Level] THEN BLANK
IF Actual[Name] IS EQUAL TO Forecast[Name] THEN IF Actual[Level] IS NOT EQUAL TO Forecast[Level] THEN "Promoted"
Actual | |
Level | Name |
Senior Manager | henry |
Senior Analyst | antonio |
Analyst | tom |
Analyst | anna |
Forecast | |
Level | Name |
Senior Manager | henry |
Analyst | antonio |
Analyst | tom |
Senior Analyst | stuart |
So in my visual I should see the measure with sometinhg like this:
My Visual | ||
Level | Name | Measure |
Senior Manager | henry | |
Senior Analyst | stuart | Removed |
Analyst | tom | |
Senior Analyst | antonio | Promoted |
Analyst | antonio | Promoted |
Analyst | anna | New |
Thanks,
Solved! Go to Solution.
Hi @cristianml ,
New a calculated table.
Table_1 = UNION(Actual,Forecast)
Add fields from above table into visual, and create below measure.
Measure = VAR _isinActual = IF ( SELECTEDVALUE ( Table_1[Name] ) IN VALUES ( Actual[Name] ), 1, 0 ) VAR _isinForecast = IF ( SELECTEDVALUE ( Table_1[Name] ) IN VALUES ( Forecast[Name] ), 1, 0 ) VAR _LevelinActual = CALCULATE ( SELECTEDVALUE ( Actual[Level] ), FILTER ( Actual, Actual[Name] = SELECTEDVALUE ( Table_1[Name] ) ) ) VAR _LevelinForecast = CALCULATE ( SELECTEDVALUE ( Forecast[Level] ), FILTER ( Forecast, Forecast[Name] = SELECTEDVALUE ( Table_1[Name] ) ) ) RETURN IF ( _isinActual = 0 && _isinForecast = 1, "Removed", IF ( _isinActual = 1 && _isinForecast = 0, "New", IF ( _isinActual = 1 && _isinForecast = 1, IF ( _LevelinActual = _LevelinForecast, BLANK (), "Promoted" ) ) ) )
Best regards,
Yuliana Gu
Hi @cristianml ,
New a calculated table.
Table_1 = UNION(Actual,Forecast)
Add fields from above table into visual, and create below measure.
Measure = VAR _isinActual = IF ( SELECTEDVALUE ( Table_1[Name] ) IN VALUES ( Actual[Name] ), 1, 0 ) VAR _isinForecast = IF ( SELECTEDVALUE ( Table_1[Name] ) IN VALUES ( Forecast[Name] ), 1, 0 ) VAR _LevelinActual = CALCULATE ( SELECTEDVALUE ( Actual[Level] ), FILTER ( Actual, Actual[Name] = SELECTEDVALUE ( Table_1[Name] ) ) ) VAR _LevelinForecast = CALCULATE ( SELECTEDVALUE ( Forecast[Level] ), FILTER ( Forecast, Forecast[Name] = SELECTEDVALUE ( Table_1[Name] ) ) ) RETURN IF ( _isinActual = 0 && _isinForecast = 1, "Removed", IF ( _isinActual = 1 && _isinForecast = 0, "New", IF ( _isinActual = 1 && _isinForecast = 1, IF ( _LevelinActual = _LevelinForecast, BLANK (), "Promoted" ) ) ) )
Best regards,
Yuliana Gu
Hi @v-yulgu-msft,
Thanks for your measure. I tried to replicate it in my model but i have a problem. My forecast table is based on a selected value cause I have 7 different versions of FORECAST. And to list the Names/and Levels I created an append table with all tables together and I take from there the list for the visual. See the pictures :
How can I solve/replicate this part ?: IN VALUES ( Forecast[Name] ), 1, 0 )
Thanks
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
101 | |
97 | |
88 | |
68 |
User | Count |
---|---|
169 | |
133 | |
130 | |
103 | |
95 |