cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michaelsh
Helper V
Helper V

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

 

Screenshot 2020-12-07 074934.png

 

6 REPLIES 6
michaelsh
Helper V
Helper V

Crossjoin! I see! Thank you, @v-stephen-msft 

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?

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.

 

Thank you, @v-stephen-msft !

I appreciate your help.

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

Hi @michaelsh ,


Sorry to disturb you...

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

 

Best Regards,
Stephen Tao

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

v-stephen-msft
Community Support
Community Support

Hi @michaelsh ,

 

1.Create three calculated tables.

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

18.png

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

19.png

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

20.png

 

2.Create visuals as follows.

21.png

 

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.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.