THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
 in SQLBI - Marco Russo (Entire Site) Search

## SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.

# Mark as Date table in Power BI #dax #powerbi

One year ago I wrote an article describing how the time intelligence DAX functions work in Power BI (I just updated the article including the example described below). In a recent event in Sydney I observed a strange (or at least unexpected) behavior of Power BI during a demo, and I think it is interesting to share a few considerations about it.

As you can read in the article linked above, I wrote that when you apply a filter to the date column of a table, and this column is used in a relationship, then the table is considered a Date table even if it is not marked as a Date table (you cannot do that in Power BI). During my demo, the Date table had two relationships: one with Sales using an integer column, and one with Purchases using a date column. My expectation was that when I was using a calculation involving only Date and Sales tables, a time intelligence filter would have required an ALL ( Date ) condition in the CALCULATE statement, but this was not the case. Let me show this with an example.

Consider this formula:

SalesYTD1 := CALCULATE ( SUM ( Sales[Amount] ), DATESYTD ( 'Date'[Date] ) )

If the relationship between Sales and Date is made using an integer column in Power BI, I would expect the formula to not provide any YTD calculation unless I rewrite it as:

SalesYTD2 := CALCULATE ( SUM ( Sales[Amount] ), DATESYTD ( 'Date'[Date] ), ALL ( 'Date' ) )

However, SalesYTD1 was working during my demo, despite I had a relationship based on a DateKey column that was an integer value (such as 20170222). After some investigation, I realized that the other relationship connecting Purchases and Date, even if not used in the two measures above, was enough to consider the Date column as a primary key of the Date table, and this produces an automatic addition of the ALL ( 'Date' ) function in the CALCULATE statement when a filter is applied to 'Date'[Date].

Thus, the lesson is that even if you cannot have Mark as Date table in Power BI, you can obtain the same result by creating an hidden empty table with a column Date in the data model, creating a 1-to-many relationship between Date[Date] and the table you created. For example, you can use MarkAsDateTable=FILTER(CALENDARAUTO(),FALSE) to obtain such a table and then create the relationship and hide the table.

You can download a working version of this example in the updated demo of the article Time Intelligence in Power BI Desktop

Published Wednesday, February 22, 2017 3:27 AM by Marco Russo (SQLBI)
Filed under: ,

#### Oxenskiold said:

While not related at all, Power BI desktop has some sort of relationship mystery as well.

If you do the following:

1) Paste a dataset (from Excel etc.) into a fact table by using the 'enter data' button in the 'Home'- fan ribbon

2) Paste a dataset (from Excel etc.) into a dimension table in the same way and NOT all of the primary keys exist in the fact table (the unknown member will have a value then)

3) create a relationship between the 2 tables.

4) Execute EVALUATE  VALUES(dimtable[dimtablecolumn]) everything is OK i.e. the unknown member is present.

5) Save and Exit Power BI desktop.

6) Launch Power BI desktop and execute EVALUATE  VALUES(dimtable[dimtablecolumn]) everything is NOT  ok  i.e. the unknown member is now NOT  present.

7) Now delete the relationship and then reestablish the same relationship. Now EVALUATE  VALUES(dimtable[dimtablecolumn]) is back to showing  the unknown member.

8) This unfortunately means  that VALUES(), SUMMARIZE(), ALL()  etc. will NEVER show the unknown member unless you do the delete/reestablish thingy when you restart Power BI Desktop.

This of course is not a problem in most real life scenarios since you'll never create a datamodel this way, however for educationally purposes it really is a problem that you cannot trust the unknown member being present.

February 22, 2017 1:59 PM

#### Oxenskiold said:

Sorry, item 2 in the list was formulated wrongly it should read:

"Paste a dataset (from Excel etc.) into a dimension table in the same way and NOT all of the foreign keys in the fact table exist in the dimension table (the unknown member will have a value then)"

February 23, 2017 3:33 AM

#### Halil G said:

so no need to import an excel file with a date table marked as date table!

March 7, 2017 2:01 PM

#### Marco Russo (SQLBI) said:

@Oxenskiold how do you query the Power BI model?

@Halil: that is another workaround, yes.

March 17, 2017 7:12 AM

#### Oxenskiold said:

@Marco

When I discovered it originally I used DAX Studio. The One table had 1 key that did not exists in the many table and the many table had a number of keys that didn't exist in the One table. We don't use the 'enter data' button anymore when we teach DAX since we have experienced that the relationship between the 2 tables is not picked up. (The one table does not filter the many table in a context transition, but it only happens occasionally and I'm not sure what triggers it)

March 20, 2017 5:43 PM

#### Marco Russo (SQLBI) said:

@Oxenskiold  I can repro it, you're right. I'll try to submit the bug...

March 30, 2017 10:44 PM

#### Oxenskiold said:

Thanks a lot Marco it's much appreciated.

April 2, 2017 5:15 AM

#### Marco Russo (SQLBI) said:

MSFT is aware of the problem and working on a fix - I report part of the notes I exchanged with them. This should be fixed in some future release of PBI Desktop.

---

The problem occurs with persistence of the referential integrity (RI) violations in 1200 mode. The server updates the metadata of the storage objects first, and only at commit does it compute the RI violations – at which point, PBI doesn’t update the storage objects and the stale values gets persisted.

The next time the model is loaded, PBI loads the stale RI violation counts for each table, and the behavior of the unknown member shows as the stale value.

Note that it would probably be sufficient to refresh just the fact table – or perhaps just alter it to add a trivial calculated column. Probably, anything that causes Vertipaq to make a change to that table should update the persisted value.

April 7, 2017 12:54 AM

#### Oxenskiold said:

Yes, that's make sense in explaining why.

Thanks for your effort Marco we really appreciate this.

April 7, 2017 6:15 AM

(required)
(required)
Submit

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.