cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Help

=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
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Help

Hi, @sameergupta60 

 

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

 

bar-1:

e1.png

 

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

 

e2.png

 

Result:

e3.png

 

Best Regards

Allan

 

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

5 REPLIES 5
Highlighted
Super User IX
Super User IX

Re: Help

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Help

Please help on above daxPlease help on above dax

 

Can you please guide on above Dax issue

Highlighted
Helper I
Helper I

Re: Help

yes, I want a power query.

Highlighted
Community Support
Community Support

Re: Help

Hi, @sameergupta60 

 

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

 

bar-1:

e1.png

 

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

 

e2.png

 

Result:

e3.png

 

Best Regards

Allan

 

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

Highlighted
Helper I
Helper I

Re: Help

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.

 

sameergupta60_0-1594101206111.png

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors