Support with DAX to create a measure column from another table

[NelioKucharski]

Hi all,

I'm stuck with this formula from few days. Tried the MS Copilot but still getting the below error:

DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

I've 2 tables:

1) FiT4 Initiative All where I've a column with text value named Phase__c. 

2) Initiative benefit, where I've a column with a Latest Estimate (LE) value (decimal number). 

I would like to have a formula that shows, in example, that if the Phase__c is "L0" then the Latest Estimate is equal to zero. This is for L0-L2. Then L3 the latest Estimate is *0.5, L4 is *0.9 and L5 and L6 equals Latest Estimate.

I've used several approaches to it, however for all of them I got the same error. Below are the formulas I've used.

Kindly appreciate if someone could provide a solution for this.

Many thanks in advance,

PoS LE =

IF (
    ISBLANK ( [Year__c] ) || [Year__c] = "",
    0,
    SWITCH (
        TRUE (),
        RELATED('FiT4 Initiative ALL'[Phase__c]) = "L0", [Latest Estimate (LE)] * 0,
        RELATED('FiT4 Initiative ALL'[Phase__c]) = "L1", [Latest Estimate (LE)] * 0,
        RELATED('FiT4 Initiative ALL'[Phase__c]) = "L2", [Latest Estimate (LE)] * 0,
        RELATED('FiT4 Initiative ALL'[Phase__c]) = "L3", [Latest Estimate (LE)] * 0.5,
        RELATED('FiT4 Initiative ALL'[Phase__c]) = "L4", [Latest Estimate (LE)] * 0.9,
        RELATED('FiT4 Initiative ALL'[Phase__c]) = "L5", [Latest Estimate (LE)],
        RELATED('FiT4 Initiative ALL'[Phase__c]) = "L6", [Latest Estimate (LE)],
        0
    )
)

 I've also created a new measure extracting the "L" and changing to value the Phase__c (renamed to 6Phase__c):

PoS LE = 
IF (
    ISBLANK ( [Year__c] ) || [Year__c] = "",
    0,
    SWITCH (
        TRUE (),
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 0, [Latest Estimate (LE)] * 0,
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 1, [Latest Estimate (LE)] * 0,
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 2, [Latest Estimate (LE)] * 0,
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 3, [Latest Estimate (LE)] * 0.5,
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 4, [Latest Estimate (LE)] * 0.9,
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 5, [Latest Estimate (LE)],
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 6, [Latest Estimate (LE)],
        0
    )
)

And based on IF formula:

PoS LE =
IF (ISBLANK ( [Year__c] ) || [Year__c] = "",
    0,
    IF (
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 0, [Latest Estimate (LE)] * 0,
        IF (
            RELATED('FiT4 Initiative ALL'[6Phase__c]) = 1, [Latest Estimate (LE)] * 0,
            IF (
                RELATED('FiT4 Initiative ALL'[6Phase__c]) = 2, [Latest Estimate (LE)] * 0,
                IF (
                    RELATED('FiT4 Initiative ALL'[6Phase__c]) = 3, [Latest Estimate (LE)] * 0.5,
                    IF (
                        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 4, [Latest Estimate (LE)] * 0.9,
                        IF (
                            RELATED('FiT4 Initiative ALL'[6Phase__c]) = 5, [Latest Estimate (LE)],
                            IF (
                                RELATED('FiT4 Initiative ALL'[6Phase__c]) = 6, [Latest Estimate (LE)],
                                0
                            )
                        )
                    )
                )
            )
        )
    )
)

And finally trying to use the "value sintax" which the error was referring to:

PoS LE = 
IF (
    ISBLANK ( [Year__c] ) || [Year__c] = "",
    0,
    SWITCH (
        TRUE (),
        VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 0, [Latest Estimate (LE)] * 0,
        VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 1, [Latest Estimate (LE)] * 0,
        VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 2, [Latest Estimate (LE)] * 0,
        VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 3, [Latest Estimate (LE)] * 0.5,
        VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 4, [Latest Estimate (LE)] * 0.9,
        VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 5, [Latest Estimate (LE)],
        VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 6, [Latest Estimate (LE)],
        0
    )
)  

[NelioKucharski · Accepted solution]

Hi all,

I just have found the error and is working fine now. Thanks for spending the time to view it. The correct formula is:

PoS LE = 
    SWITCH (
        TRUE (),
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 0, [Latest Estimate (LE)] * 0,
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 1, [Latest Estimate (LE)] * 0,
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 2, [Latest Estimate (LE)] * 0,
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 3, [Latest Estimate (LE)] * 0.5,
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 4, [Latest Estimate (LE)] * 0.9,
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 5, [Latest Estimate (LE)],
        RELATED('FiT4 Initiative ALL'[6Phase__c]) = 6, [Latest Estimate (LE)],
        0
    )
)

[bhanu_gautam]

@NelioKucharski , Try using

DAX
PoS LE =
IF (
ISBLANK ( [Year__c] ) || [Year__c] = "",
0,
SWITCH (
TRUE (),
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 0, [Latest Estimate (LE)] * 0,
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 1, [Latest Estimate (LE)] * 0,
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 2, [Latest Estimate (LE)] * 0,
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 3, [Latest Estimate (LE)] * 0.5,
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 4, [Latest Estimate (LE)] * 0.9,
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 5, [Latest Estimate (LE)],
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 6, [Latest Estimate (LE)],
0
)
)

[NelioKucharski]

Hi, this is the last formula I've posted in my message... also doesn't work