A common issue faced when working with PowerBI is the limitation of having only one Active relationship between two tables. While this is a not an issues when importing data it can make creating measures a hassle. An example of this scenario could be the use of calendar table for Order Date and Delivery Date. Both Order and Delivery Date might be FK columns in the Fact Table Orders. However at any given point in time we can have only one of them as Active. This makes it difficult to identify measures showing count of orders by OrderDate and count of Orders by DeliveryDate.
One solution to this issue is to create two calendar tables and map them individually. But this solution isn’t scalable as the number of Date Fields increase. The below screenshot shows how the relationships look inside of PowerBI.’
Notice how we have two relationships between the date and FactOrder tables. But only one is active. If we had simply used a count of order by using the order date key we would get a result as below
As we can see there are cases when the Order has been placed but not picked i.e. No Pick Date Key. And there is significant gaps between the order Date and the picked Date as well. In our report we would like to see the case for a given date how many orders were placed and how many per picked.
We can see from the above screenshot that for 23rd Aug 2014 there is one order placed with a PickedDate of 26th August 2014 but zero Orders were picked. The calculation for the PickedCount
CntByPickedDate = CALCULATE(COUNT('Fact Order'[Order Key]),
USERELATIONSHIP('Fact Order'[Picked Date Key],'Dimension Date'[Date]))There is no SQL equivalent for this function but essentially it is similar to any join where the developer selects which column to perform the join on.
Please Consider Subscribing
