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 fro...
Need Help
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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi michaelsh ,


      Sorry to disturb you...

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

       

      Best Regards,
      Stephen Tao

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

        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] ) )