Dynamically compare snapshots: before vs. after

[michaelsh]

I have a table of snapshots.

For each employee I have an attribute (department, manager, salary bin, position, etc.) and for each attribute - I have it's value.

I want my users to pick 3 things from slicers:

1. A snapshot date, 

2. Another snapshot date for comparison,

3. An attribute

And I want my results to be a table that shows how many employees "moved" from one attribute value to another between the snapshots, as shown below:

How can I dynamically do this?

https://1drv.ms/x/s!AoP_9ampPIT7-ykdCeSDHqZeb8Zn?e=fhAnrX

[michaelsh]

Crossjoin! I see! Thank you, @Anonymous 

Is there a way to make it work with some DAX measure without creating this crossjoin tables?

I have thousands of values and attributtes in my real world scenario...

I could create some disconnected tables for the measure of distinct attributes for compare, but to crossjoin the fact table seems heavy for me...

Any ideas?

[Anonymous]

Hi @michaelsh ,

According to what I have learned, it seems difficult to do without creating this crossjoin tables.

I thought about it for a long time before I came up with crossjoin.😁

Best Regards,

Stephen Tao

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

[michaelsh]

Thank you, @Anonymous !

I appreciate your help.

If no one shows up with the dynamic measure idea, I'll mark yours as a solution.

[Anonymous]

Hi @michaelsh ,

Sorry to disturb you...

Please mark my reply as solution. Thank you very much.

Best Regards,
Stephen Tao

[michaelsh]

I actually have an alternative - dynamic solution, but I haven't tested it yet.
M1 = VAR vSelectedDateBefore = SELECTEDVALUE ( SnapshotDateBefore[SnapshotDate], MIN ( SnapshotDateBefore[SnapshotDate] ) ) VAR vSelectedDateAfter = SELECTEDVALUE ( SnapshotDateAfter[SnapshotDate], MAX ( SnapshotDateAfter[SnapshotDate] ) ) RETURN COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( Data[Employee] ), "cBeforeFl", CALCULATE ( NOT ( ISEMPTY ( Data ) ), Data[SnapshotDate] = vSelectedDateBefore, Data[AttributeValue] IN VALUES ( DepartmentBefore[AttributeValue] ) ), "cAfterFl", CALCULATE ( NOT ( ISEMPTY ( Data ) ), Data[SnapshotDate] = vSelectedDateAfter, Data[AttributeValue] IN VALUES ( DepartmentAfter[AttributeValue] ) ) ), [cBeforeFl] && [cAfterFl] ) )

[Anonymous]

Hi @michaelsh ,

1.Create three calculated tables.

After =
SELECTCOLUMNS (
    'Table',
    "After", [Attribute Value],
    "AfterEmployee", [Employee],
    "AfterDate", [Snapshot Date],
    "AfterAttribute", [Attribute]
)

Before =
SELECTCOLUMNS (
    'Table',
    "Before", [Attribute Value],
    "BeforeEmployee", [Employee],
    "BeforeDate", [Snapshot Date],
    "BeforeAttribute", [Attribute]
)

Table 2 =
FILTER (
    CROSSJOIN ( 'After', 'Before' ),
    [AfterEmployee] = [BeforeEmployee]
        && [BeforeAttribute] = [AfterAttribute]
)

2.Create visuals as follows.

You can check more details from here.

Best Regards,

Stephen Tao

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