Since I want to return a value of ‘0’ if either of these two measures are blank, I chose to use the expression: ISBLANK() || ISBLANK() Note that this is not the same as records having a value of zero. Either or both of these measures could be blank if there were no data records for the current month or previous month respectively. In this case, the expression I want to evaluate is whether StoreSales is blank or if LastPeriodSales is blank. If the expression is FALSE, the third parameter is evaluated and returns its result from the function. If the expression evaluates to TRUE, the second parameter is evaluated and its result is returned from the function. It begins with a expression as the first parameter that evaluates to a Boolean value. The IF() function works much like you probably expect. For this week, I’m just going to focus on two DAX functions, IF() and ISBLANK(). Some people who have been building PowerPivot tables may not realize that DAX supports several functions that can be used to trap errors and resolve display issues like the one above. But, I still have a problem with January displaying an error message for 2007 data because there is no 2006 data in this database. When I select the field to sort by and refresh my Excel pivot table, the data appears sorted correctly with the months now appearing in a chronological order. On the very bottom of the drop down list is the field I just added. The Sort by Column dialog already knows the column that I want to display (on the left side), but prompts me for the column name by which I want to sort the label. From this menu, select the Sort by Column option as shown below. This opens a secondary fly out menu with sort options. Next, open the Column drop down menu and select the Sort option. However, by doing so, the resulting dialog box automatically knows to use that column as the column that I want to display. Strictly speaking I would not have to select the column first. I just need to know the month number so I can sort the month names by the month number to get them in the correct order.Īfter creating the month number column, I go back to the CalendarMonthLabel column and select the entire column by clicking on the column header. In the case, the day and year do not matter. In the figure below, the first several rows of data occur in the month of July and thus the function returns the number ‘7’. In this column I am going to use the MONTH() function to return the month number of the current row’s DateKey value. To fix the month order, I am going to return to the SSAS model.bim page and add a calculated column to the Date table. Some months may display error messages when no data exists in the current or previous month. The month names were listed alphabetically rather than chronologically. Last time, I left our pivot table looking like the following image with a few minor problems that needed to be fixed including:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |