HashNotes

Power BI Practice: Counting Orders by Different Dates in Power BI Using CALCULATE and USERELATIONSHIP

Mohamad Mahmood's photo
Mohamad Mahmood
·

2 min read

Scenario:

You have a Power BI model that contains two tables named Sales and Date. Sales contains four columns named TotalCost, DueDate, ShipDate, and OrderDate. Date contains one column named Date.

The tables have the following relationships:

  • Sales[DueDate] and Date[Date]

  • Sales[ShipDate] and Date[Date]

  • Sales[OrderDate] and Date[Date]

  • The active relationship is on Sales[DueDate].

You need to create measures to count the number of orders by [ShipDate] and the orders by [OrderDate]. You must meet the goal without duplicating data or loading additional data.

Solution: You create measures that use the CALCULATE, COUNT, and USERELATIONSHIP DAX functions. Does this meet the goal?

Answer: Yes, this solution meets the goal. By using the CALCULATE, COUNT, and USERELATIONSHIP DAX functions, you can create measures to count the number of orders by ShipDate and OrderDate without duplicating data or loading additional data.

Step-by-step guide

[1] Count Orders by ShipDate:

OrdersByShipDate =
CALCULATE (
    COUNT ( Sales[TotalCost] ),
    USERELATIONSHIP ( Sales[ShipDate], Date[Date] )
)

[2] Count Orders by OrderDate:

OrdersByOrderDate =
CALCULATE (
    COUNT ( Sales[TotalCost] ),
    USERELATIONSHIP ( Sales[OrderDate], Date[Date] )
)

These measures leverage the USERELATIONSHIP function to temporarily activate the relationship between Sales[ShipDate] and Date[Date] or Sales[OrderDate] and Date[Date] within the context of the CALCULATE function. This approach ensures that you can count orders based on different dates without needing to duplicate or reload data.

PowerBIpractical exercises