Help

[sameergupta60]

=IF(M4>0, "P",IF((L4-M4)>0, "AP", [@[Fin Year]])) in excel is working but BI it is not working the formula

IMC 1 = IF('bar-1'[PPCJUl]>0,"P",if('bar-1'[PPCYTD]-'bar-1'[PPCJUl]>0,"AP", 'bar-1'[Fin year]))

getting an error :-Expressions that yield variant data-type cannot be used to define calculated columns.

please guide

[v-alq-msft · Accepted solution]

Hi, @sameergupta60 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

bar-1:

If you want to create a calculated column with dax, you may try the following codes.

Result = 
IF(
    'bar-1'[PPCJU1]>0,
    "P",
    IF(
        'bar-1'[PPCYTD]-'bar-1'[PPCJU1]>0,
        "AP",
        CONCATENATE('bar-1'[Fin year],"")
    )
)

If you want to create a custom column in Power Query, you may create a custom column with the following codes.

=if [PPCJU1]>0 then "P" else if ([PPCYTD]-[PPCJU1])>0 then "AP" else Text.From([Fin year])

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sameergupta60]

thank you so much for the help.

i have one more query can you guide us.

Colour Project1 =
VAR Dept =
SELECTEDVALUE( 'bar-1'[Slab Data] )
RETURN
Switch(True(),
Dept = "P", "#FFFFFF",
Dept = "-" , "#89de21",
Dept >"0-" , "#89de21",
Dept = "`", "#FFE12D",
Dept = ".","#d9dedd",
Dept = ",","#f56342"
)

in that, I have a colour cell but unable to colour the number 22,23 in a different colour can help us.

[Greg_Deckler]

So you will likely need something like:

IF(M4>0, "P",IF((L4-M4)>0, "AP", [@[Fin Year]] & ""))

That assumes you want a DAX solution. If you want Power Query that is entirely different syntax

[sameergupta60]

yes, I want a power query.

[sameergupta60]

Please help on above dax

Can you please guide on above Dax issue