Creating a relationship between a physical and virtual table to calculate a cumulative total

[Grasshopper]

Hi All,

I'm struggling with creating the DAX code that enables the ability link a physical table (a dimension table containing a list of unique numbers from 0 > 10000) to a virtual table is based on a fact table (SVMAX_BASE_INSTALLED_PRODUCT_OBJECT) that references a column of unique machine serial number data + a column of values corresponding to the specific machine age (calculated using Datediff function based on the max date value from a date slicer and date of installation of the specific machine). The machine age needs to be calculated in DAX, given that this is a combination of a fixed value (the date of machine installation) and a user selectable date value derived from date slicer filter located on UI. The end date value of the date slicer can take any value from 01-Jan-2000 > current date.

The aim is to create a calculated measure of the cumulative/running total of machines - based on system age.

Having read a bunch of articles about virtual relationships (mostly on SQLBI) it seems the only real way of creating a relationship between a physical and virtual table is via the Filter function - referencing the temp/virtual table, looking something like this:

FILTER(

    TempTable,

    [SystemAgeCol] <= MAX(SYSTEM_AGE[SYSTEM_AGE])

)

This is the code written thus far:

CUMULATIVE_INSTALL_VS_SYSTEM_AGE_2 =

VAR SlicerMaxDate = LASTDATE(ALLSELECTED(DIM_CALENDAR_TABLE[REFERENCE_DATE]))

VAR SysAge = DATEDIFF(MIN(SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__DATE_INSTALLED__C]), SlicerMaxDate, DAY)

VAR TempTable =

ADDCOLUMNS(

    SUMMARIZE(

        SVMAX_BASE_INSTALLED_PRODUCT_OBJECT,

        SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__SERIAL_LOT_NUMBER__C]

    ),

    "SystemAgeCol",

    CALCULATE(

        SysAge

    )

)

I'm hoping I'm not too far away from the final code, but really need some guidance on next steps as there seems to be very little information on the different forums (and Youtube) on how to create relationships between physical and virtual tables.

[Grasshopper]

Hi Neeko,

Thank you for the response and apologies for the delay in reponding back!

I spent a little more time today experimenting with different implementations and just cannot seem to get this to work...I can't be unique in needing to connect a virtual table to a physical table, surely?

First Example:

CUMULATIVE_INSTALL_VS_SYSTEM_AGE =

VAR SlicerMaxDate =  LASTDATE(ALLSELECTED(DIM_CALENDAR_TABLE[REFERENCE_DATE]))

VAR SysAge = DATEDIFF(MIN(SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__DATE_INSTALLED__C]), SlicerMaxDate, DAY)

VAR TempTable =

ADDCOLUMNS(

    SUMMARIZE(

        SVMAX_BASE_INSTALLED_PRODUCT_OBJECT,

        SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__SERIAL_LOT_NUMBER__C]

    ),

    "SystemAgeCol",

    CALCULATE(

        SysAge

    )

)

RETURN

CALCULATE(

    COUNT(SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__SERIAL_LOT_NUMBER__C]),

    FILTER(

        TempTable,

        CONTAINS(

            VALUES(SYSTEM_AGE[SYSTEM_AGE]),

            SYSTEM_AGE[SYSTEM_AGE], [SystemAgeCol]

        )

    )

)

Result:

Second Example:

CUMULATIVE_INSTALL_VS_SYSTEM_AGE_EXAMPLE_02 =

VAR SlicerMaxDate =  LASTDATE(ALLSELECTED(DIM_CALENDAR_TABLE[REFERENCE_DATE]))

VAR SysAge = DATEDIFF(MIN(SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__DATE_INSTALLED__C]), SlicerMaxDate, DAY)

VAR TempTable =

ADDCOLUMNS(

    SUMMARIZE(

        SVMAX_BASE_INSTALLED_PRODUCT_OBJECT,

        SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__SERIAL_LOT_NUMBER__C]

    ),

    "SystemAgeCol",    

    SysAge

)

RETURN

CALCULATE(

    COUNT(SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__SERIAL_LOT_NUMBER__C]),

    FILTER(

        TempTable,

        [SystemAgeCol] <= MAX(SYSTEM_AGE[SYSTEM_AGE])

    )

)

Result:

Third Example:

CUMULATIVE_INSTALL_VS_SYSTEM_AGE_EXAMPLE_03 =

VAR SlicerMaxDate =  LASTDATE(ALLSELECTED(DIM_CALENDAR_TABLE[REFERENCE_DATE]))

VAR SysAge = DATEDIFF(MIN(SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__DATE_INSTALLED__C]), SlicerMaxDate, DAY)

VAR TempTable =

ADDCOLUMNS(

    SUMMARIZE(

        SVMAX_BASE_INSTALLED_PRODUCT_OBJECT,

        SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__SERIAL_LOT_NUMBER__C]

    ),

    "SystemAgeCol",

    CALCULATE(

        SysAge

    )

)

RETURN

SUMX(

    FILTER(

        TempTable,

        [SystemAgeCol] IN VALUES(SYSTEM_AGE[SYSTEM_AGE])

    ),

    COUNT([SVMXC__SERIAL_LOT_NUMBER__C])

)

Result:

Based on the results, it still appears to me that any form of relationship between the physical and virtual tables is missing as I'd expect that the filter context coming from the visual x-axis (the dimension table SYSTEM_AGE) would result in specific rows of the virtual table being selected and where the increasing age would result in an increasing count value. What I'm expecting is that the 'SystemAgeCol' created in the temp/virtual table can be joined somehow with the physical column of the dim table that contains a single column of unique ages from 1 - 10,0000 days. My expectation is that I would see a cumulative ramp corresponding to the count of systems vs. system age.

Is the above info useful? If not, then I guess I'll need to create example data so that persons are able to explore what options work as I'm out of ideas...

[Anonymous]

Hi @Grasshopper ,

Please try this:

CUMULATIVE_INSTALL_VS_SYSTEM_AGE_2 =
VAR SlicerMaxDate = LASTDATE(ALLSELECTED(DIM_CALENDAR_TABLE[REFERENCE_DATE]))
VAR SysAge = DATEDIFF(MIN(SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__DATE_INSTALLED__C]), SlicerMaxDate, DAY)
VAR TempTable =
ADDCOLUMNS(
    SUMMARIZE(
        SVMAX_BASE_INSTALLED_PRODUCT_OBJECT,
        SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__SERIAL_LOT_NUMBER__C]
    ),
    "SystemAgeCol",
    CALCULATE(
        SysAge
    )
)
RETURN MAXX(FILTER(TempTable,[SVMXC__SERIAL_LOT_NUMBER__C] in VALUES('physical table'[number])),[SystemAgeCol])

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.