Search code examples
powerbidaxpowerbi-desktop

Get the Underlying data in a measure to show in a table

I have the following data and with amazing help of Sam Nseir I sailed through a lot of problems -

UniqueID OrderDate Shipping Date Delivery Date Division Region
A174 8/18/2022 13:14 AZ AZ1
A196 11/17/2022 19:33 21-Sep-23 21-Sep-23 MP MP1
A197 1/11/2023 17:24 21-Nov-23 MP MP1
A179 5/30/2023 12:55 21-Oct-23 21-Oct-23 WB WB1
A178 6/23/2023 21:19 21-Sep-23 WB WB1
A180 6/27/2023 19:16 21-Sep-23 21-Sep-23 AZ AZ1
A195 9/21/2023 19:06 21-Oct-23 21-Oct-23 AZ AZ1

I want to show the Same table in Table Chart in PowerBI. But when I select 2023 the Order with ID A196 doesn't show up since the Active Relationship of the DateTable is with OrderDate which makes sense (Also, there are two inactive relationships with Shipping Date and Delivery Date).

How does one go about showing the data that we get in the following measure -

The Date Role table is a simple table with three rows to act as a slicer for selecting OrderDate, Shipping date and Delivery date.

What I want to do?

So, if I select Shipping Date I should get all rows with Shipping Date even if they fall out of the current DateTable context but If I select March I should get only the ones that fall in March i.e. 0.

This measure gets the counts of Such Orders -

Date Role Count = 
var oRole = CONTAINS('Date Role',  'Date Role'[Role], "OrderDate")
var sRole = CONTAINS('Date Role',  'Date Role'[Role], "Shipping Date")
var dRole = CONTAINS('Date Role',  'Date Role'[Role], "Delivery Date")

var oValues = 
    CALCULATETABLE(
        DISTINCT(Data[UniqueID]),
        NOT ISBLANK(Data[UniqueID]) = oRole
    )

var sValues = 
    CALCULATETABLE(
        DISTINCT(Data[UniqueID]),
        USERELATIONSHIP(DATETABLE[DATE], Data[Shipping Date],
        NOT ISBLANK(Data[UniqueID]) = sRole
    )

var dValues = 
    CALCULATETABLE(
        DISTINCT(Data[UniqueID]),
        USERELATIONSHIP(DATETABLE[DATE],Data[Delivery Date]),
        NOT ISBLANK(Data[UniqueID]) = dRole
    )

RETURN COUNTROWS( DISTINCT( UNION( oValues, sValues, dValues ) )
)
Solution

  • Since your OrderDate is Date/Time, you can't use this for your relationship (because the Time won't match. So you will need to create a new column of OrderDate that is just the Date.

    Create a new Calculated Column with:

    Date = DATEVAULE( [OrderDate] )

    Then update the Active relationship to use Date instead of OrderDate.

    enter image description here