In this tutorial, you learned how to calculate the number of days between multiple dates in your Fact table using a specific formula pattern, and by creating inactive relationships in your model. Master Virtual Tables In Power BI Using DAXĭiscover Multiple Product Purchases Using DAX In Power BI Conclusion How To Work With Multiple Dates In Power BI Virtually turning on the relationships between tables allows you to branch out into different formula patterns.
Under the third column, you’re only getting the number of orders per day for every row.Īs an example, you can see that there are seven orders between the two consecutive rows: If you want to get the Total Orders by Order Date, you need an active relationship between the Date table and the Data table.īy using the USERELATIONSHIP function, you’re able to virtually turn on the relationship between those two tables within the formula. It also allows you to easily turn on any relationship depending on what you need for a certain calculation. Using inactive relationships in the model is cleaner. Let’s go back to why inactive relationships are the key to making the model work. Why Using Inactive Relationships Is Important It is simply the sum of all the orders for each date that fits the criteria set by the FILTER statement in the formula. Now, the Total you see in the table has no date context. The Order Between Dates formula will use that same logic for every row in the table.
There are 477 orders that are less than the expiration date and greater than the order date. This is the result of iterating through every single order date within the 5,700 rows in the Date table. Going back to the formula, the MAX and MIN functions are simply returning the date for every row in the table. If the order date is less than and the expiration date greater than a certain day, it will count all the individual orders that belong within that date range.įor every single row in the table, there’s currently zero context coming from the model’s Fact table because there aren’t any active relationships. Next, you need a formula that can count the orders you have for a certain day: The formula uses the COUNTROWS function to sort through the Data table. Here’s how to work out the number of orders that are still live and active.įirst, you need to start with a formula for Total Orders: This visualization makes it easier to understand what the technique is doing. The time frame in the visualization is showing how many orders there are between the order and expiry dates.īetween April and July 2019, you can see a huge spike indicating that many orders have expired: To better understand how this technique works, look at this visualization: Moving forward, you need to write a formula that uses the context in the Fact table and then works through an advanced logic inside the data table. If you want to have active relationships, there are other advanced and complex methods that involve writing a new formula. Having inactive relationships is the simplest method you can use to solve this problem. When you want to calculate a logic that involves counting elements that are in progress, you need to have inactive relationships. The context can vary depending on what you’re trying to solve, but the technique will be the same.įor the calculations to be correct, you can’t have active relationships in the model. The formula needs to count information that sits between multiple dates. In this case, live means orders that haven’t expired. Now, the calculation needs to show the number of orders that are live for specific dates. The great thing about this technique is its ability to be used in other similar scenarios. And two, the calculations should have the correct DAX formulas. Setting up the model can be very confusing. This kind of table can exist for order dates, expiration dates, and request dates, among others. Let’s look at the model of the demo data set for this tutorial.
0 kommentar(er)
