Dax calculations to aggregate sum of rows within separate relationships

[royalT_83]

Hello, I am new to DAX and I am currently creating a dashboard to aggregate volume based on multiple dates (order created, ship date, service date, delivery date). The main relationship is from the order created date and the numbers that are reflected only aggregates based on the value that exists for the order date. There are instances where I have dates that fall within the selected slicer but they are not counted in the aggregate for an order being created.

Example: The Date filter is set to 10/4/22 (based on Order Date) and a shipment occurred on that day for an order, but the order was created on 10/1/22. The order appears within the data details since there is a relationship from the shipment date but PowerBI does not include that count for the order being created since it was created outside of 10/4/22.

I want to be able to count the total number of orders that are reflected if a date is present in any of the date fields (slicer counts if any date condition is true). Does anybody know if there is a way in powerBI to aggregate the number of rows for a field even if the number was outside of the date the order was created but had an event that occurred within that date?

[OwenAuger · Accepted solution]

Hi @royalT_83 

Assuming you have an active relationship between Order Created Date and 'Date'[Date], and inactive relationships between other date columns and 'Date'[Date], you could use a pattern like the one below.

In my test model, there is an active relationship with Order Date and inactive relationship with Delivery Date.

This measure returns Sales Amount where either Order Date or Delivery Date (or both) falls within the 'Date' filter.

Sales Amount by Order Date or Delivery Date =
VAR DatesOrder =
    SUMMARIZE ( Sales, Sales[Order Date], Sales[Delivery Date] )
VAR DatesDelivery =
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[Order Date], Sales[Delivery Date] ),
        USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] )
    )
VAR DatesCombined =
    UNION ( DatesOrder, DatesDelivery )
VAR Result =
    CALCULATE (
        [Sales Amount],
        -- Replace with required measure
        REMOVEFILTERS ( 'Date' ),
        DatesCombined
    )
RETURN
    Result

An alternative method would be to set up the model with a kind of unpivoted junk dimension containing the existing combinations of dates from the fact table. I'll post an example of that when I have time later.

Regards

[v-pnaroju-msft]

Hi royalT_83,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi royalT_83,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution to guide other members.

Thank you.

[v-pnaroju-msft]

Hi royalT_83,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @OwenAuger, for your response.

Hi @royalT_83,

We appreciate your inquiry posted on the Microsoft Fabric Community Forum.

Please find attached the screenshot and the PBIX file, which may help in resolving the issue:

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who face similar queries.

Should you have any further questions, please feel free to contact the Microsoft Fabric community.

Thank you.

[OwenAuger · Accepted solution]

Hi @royalT_83 

Assuming you have an active relationship between Order Created Date and 'Date'[Date], and inactive relationships between other date columns and 'Date'[Date], you could use a pattern like the one below.

In my test model, there is an active relationship with Order Date and inactive relationship with Delivery Date.

This measure returns Sales Amount where either Order Date or Delivery Date (or both) falls within the 'Date' filter.

Sales Amount by Order Date or Delivery Date =
VAR DatesOrder =
    SUMMARIZE ( Sales, Sales[Order Date], Sales[Delivery Date] )
VAR DatesDelivery =
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[Order Date], Sales[Delivery Date] ),
        USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] )
    )
VAR DatesCombined =
    UNION ( DatesOrder, DatesDelivery )
VAR Result =
    CALCULATE (
        [Sales Amount],
        -- Replace with required measure
        REMOVEFILTERS ( 'Date' ),
        DatesCombined
    )
RETURN
    Result

An alternative method would be to set up the model with a kind of unpivoted junk dimension containing the existing combinations of dates from the fact table. I'll post an example of that when I have time later.

Regards

[royalT_83]

This appears to be in the ball park on what I am trying to do and the code makes sense. I will make some tweaks and will confirm that everything is working properly.