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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Beyondforce
Helper I
Helper I

IF statment doesn't work!

Hey Guys,

 

I'm trying to add a new column and I'm also adding this formula, but it's not working! What's wrong with it?

=IF([LateStart]= 0, && ([LateEnd] = 0),0))

 

Thanks.

Ben.

1 ACCEPTED SOLUTION

@Beyondforce

 

Hi, not the best but works

 

If.png




Lima - Peru

View solution in original post

16 REPLIES 16

@Beyondforce Try the following. I assume you want the column to return 0 if both LateEnd and/or LateStart = 0

 

Column = IF(AND([LateStart]= 0, [LateEnd] = 0),0)

I'm also assuming that these are columns. Ideally it should read to include the table name, not using the table name implies a measure only:

 

Column = IF(AND('Table1'[LateStart]= 0, 'Table1'[LateEnd] = 0),0)

Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hi @Seth_C_Bauer,

 

Thanks for your replay. I have tried both option, but I kepp getting the same error: "Expression.Error: The name 'IF' wasn't recognized.  Make sure it's spelled correctly."

@Beyondforce Are you creating a Calculated column?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

I have 2 columns with 1 and 0. In the new column I want to create 4 option:

LateStart    LateEnd    Status

0                     0              0
1                     0              1
0                     1              2
1                     1              3

 

I need to use the IF and AND statment to create the result in the "Status" column. Later I'll use a color condition for all the 4 number options in the Status column.

@Beyondforce

Status = IF(AND('Table1'[LateStart] = 0, 'Table1'[LateEnd]= 0), 0,
	IF(AND('Table1'[LateStart] = 1, 'Table1'[LateEnd]= 0), 1,
	IF(AND('Table1'[LateStart] = 0, 'Table1'[LateEnd]= 1), 2,
    IF(AND('Table1'[LateStart] = 1, 'Table1'[LateEnd]= 1), 3, 99)
)
))

 

You can use the above if you are in the model using DAX. (Noted after seeing VV posted.) 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer, I have tried you  script, but it still doesn't work!

@Vvelarde I have tried your script and it's working 🙂

 

Thank you both for your time and help.

 

Cheers.

@Beyondforce There are two parts to the Power BI Desktop. The screenshot you posted shows that you are in "Edit Queries" - this utilizes the "M" language - and that solution was provided by @Vvelarde because he was smart enough to ask where you were trying this 🙂 Once you "Close & Apply" then you are loading the data into the Tabular model front end. From here on out you need to use the "DAX" language when building measures or calculated columns. My solution works as a calculated column in DAX.

As you move back and forth between each area, just keep in mind the language changes.

 

Hope this helps out as you move forward!


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer, Maybe you can help me with something else!

The table that I'm working on, comes from a CSV dump file (from a Main Frame). The log file doesn't count weekends, because nothing happends on weekends. But I want to add them anyway Automaticaly!

Which means, I'm going to have empty rows on the weekends rows, which is ok. Is it possible to do or would I have to create a new table for that (I hope not!)?

 

Thanks.

 

@Beyondforce If you want to display all days, you should build a date table. Then depending on what measure or calculation you use I believe you throw a "+0" at the end of your calculation to return 0 instead of "Blank"

 

Here is a dynamic date table that you can build in the model part in DAX. The 'FactTable' is where your dates are currently through today.

Date =
ADDCOLUMNS (
CALENDAR (MINX('FactTable', [Created Date]), NOW()),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hi @Seth_C_Bauer,

 

Thank you very much. It it very helpfull!

I was wondering if you don't mind checking out my other post. Maybe you could think of a better idea on how to do this. I was thinking maybe, if I create new tables from each column and then build relationships, that would be a good approach, I'm not sure!?

http://community.powerbi.com/t5/Desktop/Calculating-data-from-another-table-Not-Simple/m-p/183469#M8...

 

I really appreciate your help!

 

Ben.

@Beyondforce Just looking at it quickly, it seems that you should just be able to relate the transaction date to the new date table I outline above. I assume the table you are creating is a visual...  It should be really straightforward from there.

The date table will have year and month name

you can create a measure like this: countdate = COUNTA('Date'[Date]) for the days of month

Then it sounds like some straightforward counts on dates or other fields to round out the solution.

 

If this is over-simplifying, let me know - but the date table will do most of the legwork for the year/month/count of days for sure.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer, I have found a better way... I have asked the guy who created those files, to add the extra columns that I need 😉

 

Thanks for the help.

Thanks @Seth_C_Bauer.

 

I have just started working and playing with Power BI and I still need to learn the differnces between M language and DAX, and when to use what!

I will try your idea as well as soon as I figure out where to put the code 😉

@Beyondforce

 

Are you doing this in Query Editor? or in a Calculated Column?




Lima - Peru

In here:

2017-05-26_232744.png

@Beyondforce

 

Hi, not the best but works

 

If.png




Lima - Peru

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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