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
cro1fni
Frequent Visitor

Find records that exist in one sliced table, but not the another

Hello everyone,

 

I have two tables that are made up of the same historical HR data. Each month, new data is loaded into the tables. I want the user to be able to select two different months and compare the HR data between the two. For example, the user selects June 2021 in the slicer and one table (Headcount_Table_Start) reflects June data. They select July 2021 in another slicer and the second table (Headcount_Table_End) reflects July data. 

I would like to identify additions and terminations between the two months. If a specific employees personnel number exists in July 2021 data, but not June 2021 data, I want to flag that somehow. The ulatimate goal is to have two tables that would summarize additions and terminations by personnel number and other employee information.

I have a page that summarizes general headcount that works with the slicer, but am confused on how to achieve my additions and terminations goal.

 

Any help is appreciated. Thank you!

1 ACCEPTED SOLUTION

OK, so it's just the one source table. That changes things.

Let's see if this works for you:

1) Crate 2 slicer tables for the Start Period and Comparison Period. Keep these unrelated in the model. It should look like this:

Modelv2.JPG

Next create the following measure for the tables:

For both the "Added Associates" & Terminated Associates":

 

Month End = IF(ISINSCOPE('HR Table'[Personnel no]), MAX('HR Table'[Month End]))

 

For the "Added Associates" visual

 

Added Associates =
VAR StartP =
    SELECTCOLUMNS (
        FILTER (
            'HR Table',
            'HR Table'[Month.Year] = SELECTEDVALUE ( 'Start Period'[Month.Year] )
        ),
        "PN", 'HR Table'[Personnel no],
        "DIL", 'HR Table'[Dir/Ind Labor],
        "RS", 'HR Table'[Reporting Site],
        "PDCL", 'HR Table'[PDCL]
    )
VAR EndP =
    SELECTCOLUMNS (
        FILTER (
            'HR Table',
            'HR Table'[Month.Year] = SELECTEDVALUE ( 'Comparison Period'[Month.Year] )
        ),
        "PN", 'HR Table'[Personnel no],
        "DIL", 'HR Table'[Dir/Ind Labor],
        "RS", 'HR Table'[Reporting Site],
        "PDCL", 'HR Table'[PDCL]
    )
RETURN
    COUNTROWS ( EXCEPT ( EndP, StartP ) )

 

For the "Terminated Associates"

 

Terminated Associates =
VAR StartP =
    SELECTCOLUMNS (
        FILTER (
            'HR Table',
            'HR Table'[Month.Year] = SELECTEDVALUE ( 'Start Period'[Month.Year] )
        ),
        "PN", 'HR Table'[Personnel no],
        "DIL", 'HR Table'[Dir/Ind Labor],
        "RS", 'HR Table'[Reporting Site],
        "PDCL", 'HR Table'[PDCL]
    )
VAR EndP =
    SELECTCOLUMNS (
        FILTER (
            'HR Table',
            'HR Table'[Month.Year] = SELECTEDVALUE ( 'Comparison Period'[Month.Year] )
        ),
        "PN", 'HR Table'[Personnel no],
        "DIL", 'HR Table'[Dir/Ind Labor],
        "RS", 'HR Table'[Reporting Site],
        "PDCL", 'HR Table'[PDCL]
    )
RETURN
    COUNTROWS ( EXCEPT ( StartP, EndP ) )

 

Add a slicer for the Start Period and Comparison Period.

Add the fields from the HR table and the relevant measures for each table. In both tables, select the Added Associates and Terminated Associates in the filter pane and set the value to "Is not blank"

Filter pane.JPG

Create the following slicer to filter out the values for Year.Month <= the selected value in the Start Period slicer:

 

Filter Comp Slicer =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( 'Comparison Period'[Month.Year] ),
        FILTER (
            'Comparison Period',
            'Comparison Period'[Month.Year] > SELECTEDVALUE ( 'Start Period'[Month.Year] )
        )
    )
)

 

Add this measure to the Filters for the visual in the filter pane and set the value to greater or equal to 1:

Filter slicer.JPG

And you will get this:

CompHrTables2.gif

Beware that the result differs from your because there is the following in the sample data:

Result.JPG

I've attached the sample PBIX file

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

12 REPLIES 12
PaulDBrown
Community Champion
Community Champion

Ok see if this works for you.

My sample tables:

 

Start TableStart TableEnd TableEnd Table

Create the following model with dimension tables for Personel No, Functional Area and Dir/Ind Labour

Model.JPG

 

Now create the following measures:

Rogue Rows =
VAR STartT =
    SELECTCOLUMNS (
        'Start Table',
        "No", 'Start Table'[Personnel no],
        "Area", 'Start Table'[Functional Area],
        "DirInd", 'Start Table'[Dir/Ind Labour]
    )
VAR EndT =
    SELECTCOLUMNS (
        'End Table',
        "No", 'End Table'[Personnel no],
        "Area", 'End Table'[Functional Area],
        "DirInd", 'End Table'[Dir/Ind Labour]
    )
VAR STR =
    EXCEPT ( STartT, EndT )
VAR ETR =
    EXCEPT ( EndT, STartT )
RETURN
    COUNTROWS ( UNION ( STR, ETR ) )
Activity Status =
VAR STartT =
    SELECTCOLUMNS (
        'Start Table',
        "No", 'Start Table'[Personnel no],
        "Area", 'Start Table'[Functional Area],
        "DirInd", 'Start Table'[Dir/Ind Labour]
    )
VAR EndT =
    SELECTCOLUMNS (
        'End Table',
        "No", 'End Table'[Personnel no],
        "Area", 'End Table'[Functional Area],
        "DirInd", 'End Table'[Dir/Ind Labour]
    )
VAR STR =
    EXCEPT ( STartT, EndT )
VAR ETR =
    EXCEPT ( EndT, STartT )
RETURN
    IF (
        ISINSCOPE ( 'Dim PersNo'[Personnel no] ),
        IF (
            COUNTROWS ( STR ) = 1,
            "In Start but not End",
            IF ( COUNTROWS ( ETR ) = 1, "New" )
        )
    )

Next create a table visual using the fields from the dimension tables for Personnel No,, Functional Area & Dir/Ind Labour and include the above measures. Create a slicer from the Month.Year field in you Start Table and another slicer from the Mont.Year field for the End Table.

And you will get the following:

CompHrTables.gif

(If you wish, you can also make the End Slicer show values wich are higher than the value selected in the Start Slicer)

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hello @PaulDBrown ,

 

This definitely helps! However, I am having one issue and I have a couple questions. First, do I need an individual dimension table for each field? I have multiple other fields that I have not shared in the example table, so would I need a dimension table for each of those as well? 

 

The issue I have is that some personnel numbers are repeated in the tables. There is one line for each personnel number and product line then are working with, but a personnel may work with multiple lines. The associates working with multiple product lines do not appear to be caught by the measures. Would this be solved by adding a dimension table for product lines?

 

Thank you!

Happy we are getting there!

To answer your questions:

1) Dimension tables: you need dimension tables for the fields which you wish to compare rows by. As you probably noticed, the trick is to build the "Rogue row" table using the dimension tables. 

2) If Product line disciminates rows, then you also need a dimension table.

We will also have to adapt the measures to take these other fields into account.

 

It would would be really helpful if you could post sample data of both tables (hide confidential data) - you can just create samples in Excel and paste them into your message here -  and explain which fields determine whether make the row different.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown ,

Here is some sample data. Those with the "add" in the right most column and the "term" in the right most column would be the lines I would like to show in some sort of summary table when compare 07.2021 to 08.2021. The fields that make a column unique would be personnel no, reporting site, and PDCL ultimately. The PDCL Split value is linked to PDCL. All other columns would be linked to the personnel number, regardless of PDCL.

 

Personnel noEmpl/appl nameOrg Unit AbbrPosition DescriptionCost CenterWork SiteSupervisorDir/Ind LaborFunctional AreaEmpl CategoryReporting SitePDCLPDCL SplitMonth EndYear AddedMonth.Year 
62299699Brandon, MarkLogisticsLogistics Associate I12South CarolinaJones, BobDirectPPCRegular1MAP0.5820218.2021add
62299699Brandon, MarkLogisticsLogistics Associate I12South CarolinaJones, BobDirectPPCRegular1HLD0.5820218.2021add
62294970Monet, LisaLogisticsLogistics Associate18North CarolinaSmith, AngelaDirectPPCRegular1LMT0.5820218.2021add
62294970Monet, LisaLogisticsLogistics Associate18North CarolinaSmith, AngelaDirectPPCRegular1AST0.5820218.2021add
62296960Elkin, JoshProductionTrainee9South CarolinaSmith, AngelaDirectPPCRegular1MAP1820218.2021add
49000348Macintosh, RobTestingContract45North CarolinaSmith, AngelaIndirectAdminLeasing Employee3LMT0.5620216.2021 
49000348Macintosh, RobTestingContract45North CarolinaSmith, AngelaIndirectAdminLeasing Employee3AST0.5620216.2021 
49000348Macintosh, RobTestingContract45North CarolinaSmith, AngelaIndirectAdminLeasing Employee3LMT0.5720217.2021term
49000348Macintosh, RobTestingContract45North CarolinaSmith, AngelaIndirectAdminLeasing Employee3AST0.5720217.2021term
49000598Landon, SarahManufacturingScrew/Shaft Proc91North CarolinaSmith, AngelaDirectPPCLeasing Employee3AST1620216.2021 
49000598Landon, SarahManufacturingScrew/Shaft Proc91North CarolinaSmith, AngelaDirectPPCLeasing Employee3AST1720217.2021term
91885354Nix, AustinManufacturingMachinist-in-Training61South CarolinaSmith, AngelaDirectPPCRegular2MAP1720217.2021 
91885354Nix, AustinManufacturingMachinist-in-Training61South CarolinaSmith, AngelaDirectPPCRegular2MAP1820218.2021 
91885354Nix, AustinManufacturingMachinist-in-Training61South CarolinaSmith, AngelaDirectPPCRegular2MAP1620216.2021 
91852486Proctor, JoeManufacturingMachinist - Milling85North CarolinaSmith, AngelaDirectPPCRegular2AST1720217.2021 
91852486Proctor, JoeManufacturingMachinist - Milling85North CarolinaSmith, AngelaDirectPPCRegular2AST1820218.2021 
91852486Proctor, JoeManufacturingMachinist - Milling85North CarolinaSmith, AngelaDirectPPCRegular2AST1620216.2021 
62051803Cromer, Tristan  FinanceAssociate Business Analyst97North CarolinaJones, BobIndirectAdminRegular1NBU1820218.2021 
62051803Cromer, Tristan  FinanceAssociate Business Analyst97North CarolinaJones, BobDirectAdminRegular1NBU1720217.2021 
62051803Cromer, Tristan  FinanceAssociate Business Analyst97North CarolinaJones, BobIndirectAdminRegular1NBU1620216.2021 

 

OK, so it's just the one source table. That changes things.

Let's see if this works for you:

1) Crate 2 slicer tables for the Start Period and Comparison Period. Keep these unrelated in the model. It should look like this:

Modelv2.JPG

Next create the following measure for the tables:

For both the "Added Associates" & Terminated Associates":

 

Month End = IF(ISINSCOPE('HR Table'[Personnel no]), MAX('HR Table'[Month End]))

 

For the "Added Associates" visual

 

Added Associates =
VAR StartP =
    SELECTCOLUMNS (
        FILTER (
            'HR Table',
            'HR Table'[Month.Year] = SELECTEDVALUE ( 'Start Period'[Month.Year] )
        ),
        "PN", 'HR Table'[Personnel no],
        "DIL", 'HR Table'[Dir/Ind Labor],
        "RS", 'HR Table'[Reporting Site],
        "PDCL", 'HR Table'[PDCL]
    )
VAR EndP =
    SELECTCOLUMNS (
        FILTER (
            'HR Table',
            'HR Table'[Month.Year] = SELECTEDVALUE ( 'Comparison Period'[Month.Year] )
        ),
        "PN", 'HR Table'[Personnel no],
        "DIL", 'HR Table'[Dir/Ind Labor],
        "RS", 'HR Table'[Reporting Site],
        "PDCL", 'HR Table'[PDCL]
    )
RETURN
    COUNTROWS ( EXCEPT ( EndP, StartP ) )

 

For the "Terminated Associates"

 

Terminated Associates =
VAR StartP =
    SELECTCOLUMNS (
        FILTER (
            'HR Table',
            'HR Table'[Month.Year] = SELECTEDVALUE ( 'Start Period'[Month.Year] )
        ),
        "PN", 'HR Table'[Personnel no],
        "DIL", 'HR Table'[Dir/Ind Labor],
        "RS", 'HR Table'[Reporting Site],
        "PDCL", 'HR Table'[PDCL]
    )
VAR EndP =
    SELECTCOLUMNS (
        FILTER (
            'HR Table',
            'HR Table'[Month.Year] = SELECTEDVALUE ( 'Comparison Period'[Month.Year] )
        ),
        "PN", 'HR Table'[Personnel no],
        "DIL", 'HR Table'[Dir/Ind Labor],
        "RS", 'HR Table'[Reporting Site],
        "PDCL", 'HR Table'[PDCL]
    )
RETURN
    COUNTROWS ( EXCEPT ( StartP, EndP ) )

 

Add a slicer for the Start Period and Comparison Period.

Add the fields from the HR table and the relevant measures for each table. In both tables, select the Added Associates and Terminated Associates in the filter pane and set the value to "Is not blank"

Filter pane.JPG

Create the following slicer to filter out the values for Year.Month <= the selected value in the Start Period slicer:

 

Filter Comp Slicer =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( 'Comparison Period'[Month.Year] ),
        FILTER (
            'Comparison Period',
            'Comparison Period'[Month.Year] > SELECTEDVALUE ( 'Start Period'[Month.Year] )
        )
    )
)

 

Add this measure to the Filters for the visual in the filter pane and set the value to greater or equal to 1:

Filter slicer.JPG

And you will get this:

CompHrTables2.gif

Beware that the result differs from your because there is the following in the sample data:

Result.JPG

I've attached the sample PBIX file

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown 

 

This is extremely helpful! It appears to be working with my data set, so thank you so much. If I'm understanding correctly, the fields that are used within the "Added Associates" table will be the fields that the "Added Associates" measure will use to find differences between the months?

 

I have one more question. With this setup, will comparing between years work? For example, will it be possible to compare between 06.2021 and 08.2022, or will I need to add a "Year End" measure similar to the "Month End" measure?

If I'm understanding correctly, the fields that are used within the "Added Associates" table will be the fields that the "Added Associates" measure will use to find differences between the months?

That is correct.

With this setup, will comparing between years work? For example, will it be possible to compare between 06.2021 and 08.2022, or will I need to add a "Year End" measure similar to the "Month End" measure?

The way the measures work now is that the comparison is between the actual periods selected in both slicers. So yes, you can compare 06.2021 and 08.2022. Just beware that the comparison is between the data each period itself contains.

BTW I've just realised that I didn't change the title in the bottom table to "Terminated Associates". I'll edit the previous post to avoid confusion.

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown 

 

Great! Thank you so much for your help Paul!

v-henryk-mstf
Community Support
Community Support

Hi @cro1fni ,

 

According to your description, if there is a change in the data in table B, the number of rows may be inconsistent with the number of rows in table A. Therefore, for the formula you create, using the SUMMARIZE function cannot combine the two columns to return the desired result.

 

My suggestion is to use different slicers for the two tables, use the following functions, specify the visual filtered by the slicer and compare the results.

 

vhenrykmstf_0-1630983661369.png

 


If the problem is still not resolved, please provide some test data (delete sensitive information), I will answer you as soon as possible. Looking forward to your reply.


Best Regards,
Henry


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

Hello @v-henryk-mstf ,

 

Thank you for the response. I'm not quite sure how to use this in my situation. Could you please elaborate?

 

I currently use two tables, but both have the same data source. Each is linked to its own slicer to allow for the user to select to month end snapshots to compare. The goal would be to have the following table summarize the data in my Power BI.

cro1fni_0-1631030066284.png

 

In addition, I would like to have two tables that summarize the additions and terminations when comapring the two months. I have provided sample data below for that and the intended output.

 

Personnel noDir/Ind LaborFunctional AreaPDCLMonth EndYear AddedMonth.Year
91266918IndirectSalesEDC520215.2021
62043180IndirectSalesEDC520215.2021
49000597DirectSalesAST620216.2021
49000597DirectSalesLMT620216.2021
91828320DirectPPCMAP620216.2021
91266918IndirectSalesEDC620216.2021
62043180IndirectSalesEDC620216.2021
62074606DirectPPCHLD620216.2021
62074606DirectPPCMHS620216.2021
62074606DirectPPCMAP620216.2021
49000597DirectSalesAST720217.2021
49000597DirectSalesLMT720217.2021
91828320DirectPPCMAP720217.2021
91266918IndirectSalesEDC720217.2021
62043180IndirectSalesEDC720217.2021
62074606DirectPPCHLD720217.2021
62074606DirectPPCMHS720217.2021
62074606DirectPPCMAP720217.2021
62283826DirectPPCMAP820218.2021
62291670DirectSalesLMT820218.2021
62291670DirectSalesAST820218.2021
91828320DirectPPCMAP820218.2021
91266918IndirectSalesEDC820218.2021
62043180IndirectSalesEDC820218.2021
62074606DirectPPCMAP820218.2021
62074606DirectPPCHLD820218.2021
62074606DirectPPCMHS820218.2021

 

cro1fni_1-1631030178329.png

 

cro1fni_2-1631030186727.png

 

 

cro1fni
Frequent Visitor

To provide more detail, here is an example of the Headcount_Table_Start table after the slicer.

 

cro1fni_1-1630508491876.png

 

 

And this would be the Headcount_Table_End table after the slicer.

cro1fni_2-1630508555455.png

 

The goal is to return a table that would look show additions to staff (in this example, 62290790):

 

62290790 |  Admin  |  Indirect  |  7.2021

 

Additionally, I would like to count the number of additions. 

 

Currently, I have tried the following dax expression to identify additions and terminations. It works if there is only one addition or termination, but I get an error when there are multiple.

 

cro1fni_3-1630508735314.png

 

 

PaulDBrown
Community Champion
Community Champion

Please follow the recommendations outlined in the thead to help us help you:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.