Forum Discussion

michaelsh's avatar
michaelsh
Icon for Kudo Kingpin rankKudo Kingpin
5 years ago

Dynamically compare snapshots: before vs. after

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

 

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • michaelsh's avatar
    michaelsh
    Icon for Kudo Kingpin rankKudo Kingpin
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        michaelsh
        Icon for Kudo Kingpin rankKudo Kingpin
        5 years ago

        Thank you, Anonymous !

        I appreciate your help.

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