Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
michaelsh
Kudo Kingpin
Kudo Kingpin

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
Kudo Kingpin
Kudo Kingpin

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.