Add a comment. Gordon Linoff Gordon Linoff 1. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually.
For example, an alternative way of detecting whether to force zero checks whether the currently selected range intersects with the dates between FirstSaleEver and LastSaleEver :.
This code runs fast and it works just fine. Still, there is a problem with it. The logic that defines whether a product should show zero or not is based on the Sales table. We determine whether to show a zero or not based on the presence of sales. This detail is basically a negation of our initial requirement: we wanted to be able to control when to show zero independently from Sales Amount. Indeed, if a product were introduced on the market for example on the first of December, we want to start showing zeroes from the first of December even if the first sale of the product were on the 15 th of December.
With the current implementation, this is not possible. A better solution would be to store the information about the starting date in a separate table. If the logic is based on the product only — as it is in our scenario — then a column in the Product table would be just fine. In that case, to determine the starting date to produce zeroes one could use a MIN of the column in Product. In a more complex scenario, you might have more sophisticated rules. For example, the starting date for zeroes might depend on the store, on the country, or on combinations of different dimensions.
In the more generic scenario, the best option is to avoid adding the logic to the DAX code and to rely on a physical table instead.
As an example, we created the following table using DAX, even though it is better to create it with Power Query to avoid the danger of circular dependencies:.
The table contains one row for each combination of date and product for which we want to show zero instead of BLANK. Later in the article, we show how to reduce its size if needed. We need to create relationships with the relevant dimensions.
In our case, they are Product and Date. Once the model is completed, the measure no longer needs to perform calculations over dates. Therefore, the code becomes much simpler:. The result is the very same as the previous measure. The main advantage of using the ZeroGrain table is that the logic to decide whether to show BLANK or zero can now be much more complex.
The price, in terms of performance, is due at process time and no longer at query time. If the size of the ZeroGrain table is too large, you can change its granularity.
For example, a good option would be to create the ZeroGrain table at the month granularity instead of the day granularity. By following this path, you reduce the size of the ZeroGrain table but, at the same time, you lose the option of using a regular strong relationship between Date and ZeroGrain.
You are commenting using your Google account. You are commenting using your Twitter account. You are commenting using your Facebook account. Notify me of new comments via email. Notify me of new posts via email. Email Address:. Feeds: Posts Comments. Rate this:. Like this: Like Loading
0コメント