Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
cristianml
Post Prodigy
Post Prodigy

Dax measure between 2 tables (New, Removed, Promoted)

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
LevelName
Senior Managerhenry
Senior Analystantonio
Analysttom
Analystanna

 

Forecast
LevelName
Senior Managerhenry
Analystantonio
Analysttom
Senior Analyststuart

 

So in my visual I should see the measure with sometinhg like this:

 

My Visual
LevelNameMeasure
Senior Managerhenry 
Senior AnalyststuartRemoved
Analysttom 
Senior AnalystantonioPromoted
AnalystantonioPromoted
AnalystannaNew

 

Thanks,

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @cristianml ,

 

New a calculated table.

Table_1 = UNION(Actual,Forecast)

1.PNG

 

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" )
            )
        )
    )

2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @cristianml ,

 

New a calculated table.

Table_1 = UNION(Actual,Forecast)

1.PNG

 

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" )
            )
        )
    )

2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 :

NAME MEASURE.jpg

comparison.jpg

 

How can I solve/replicate this part ?:  IN VALUES ( Forecast[Name] ), 1, 0 )

 

Thanks 

 

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.