Forum Discussion

Beyondforce's avatar
Beyondforce
Helper I
9 years ago
Solved

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.
Beyondforce's avatar
Beyondforce
Helper I
9 years ago

Anonymous, 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.

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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" )
)
  • Beyondforce's avatar
    Beyondforce
    Helper I
    9 years ago

    Hi Anonymous,

     

    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#M80539

     

    I really appreciate your help!

     

    Ben.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.

  • Beyondforce's avatar
    Beyondforce
    Helper I
    9 years ago

    Anonymous, 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.