Create two list from same parent table with difference conditon.

[AshishTanwar88]

Hi,

I have the main table having details of the employees present on a given date. Now I need to create a report with two date slicer (done) and two lists having the employee details as per the below notes:

Note: Lora was absent on 1/22/2025 and Martin was absent on 1/24/2025

Report requirement:

Result Table 1 should have the Employee present on [Date Filter 1] but not [Date Filter 2]

Martin in the example above

Result Table 2 should have the Employee present on [Date Filter 2] but not [Date Filter 1]

Lora in the example above

Please help, Thank you In advance.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[AshishTanwar88]

Hi @Ashish_Mathur 

Here is the sample data. Thank you for looking into this.

	Report Data
Em ID	AttendanceDate	Emp name
E01	1/22/2025	Sam
E02	1/22/2025	Martin
E04	1/22/2025	Smith
E01	1/23/2025	Sam
E02	1/23/2025	Martin
E03	1/23/2025	Lora
E04	1/23/2025	Smith
E01	1/24/2025	Sam
E03	1/24/2025	Lora
E04	1/24/2025	Smith

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[AshishTanwar88]

Thanks alot @Ashish_Mathur, working perfectly 🙂

[Ashish_Mathur]

You are welcome.

[FarhanJeelani]

Dear @AshishTanwar88 ,

Try below steps:

Load the Data:

Load the source table into Power BI.

Create Two Date Slicers:

Add a slicer for AttendanceDate in the Power BI report. Duplicate this slicer to create two independent slicers for Date Filter 1 and Date Filter 2

Create Calculated Tables:

Use DAX to create two calculated tables for Result Table 1 and Result Table 2.

DAX for Result Table 1

This table includes employees present on Date Filter 1 but absent on Date Filter 2:

ResultTable1 = 
VAR FilterDate1 = SELECTEDVALUE(SlicerTable1[AttendanceDate])
VAR FilterDate2 = SELECTEDVALUE(SlicerTable2[AttendanceDate])
RETURN
FILTER(
    MainTable,
    MainTable[AttendanceDate] = FilterDate1 &&
    NOT(MainTable[Em ID] IN 
        CALCULATETABLE(
            VALUES(MainTable[Em ID]),
            MainTable[AttendanceDate] = FilterDate2
        )
    )
)

DAX for Result Table 2

This table includes employees present on Date Filter 2 but absent on Date Filter 1:

ResultTable2 = 
VAR FilterDate1 = SELECTEDVALUE(SlicerTable1[AttendanceDate])
VAR FilterDate2 = SELECTEDVALUE(SlicerTable2[AttendanceDate])
RETURN
FILTER(
    MainTable,
    MainTable[AttendanceDate] = FilterDate2 &&
    NOT(MainTable[Em ID] IN 
        CALCULATETABLE(
            VALUES(MainTable[Em ID]),
            MainTable[AttendanceDate] = FilterDate1
        )
    )
)

Add the Tables to the Report:

• Use a table visual in Power BI to display the data from Result Table 1 and Result Table 2.

• Customize the Report:

Add titles and labels to clearly indicate what each table represents.

Result

• Result Table 1: Displays employees present on Date Filter 1 but absent on Date Filter 2 (e.g., Martin)
• Result Table 2: Displays employees present on Date Filter 2 but absent on Date Filter (e.g., Lora)

Please mark this as solution if it helps you. Appreciate Kudos.

[AshishTanwar88]

Thanks for your response on this, I am following your solution but some how it is not working at my end, below is my DAXs to create the tables:

ResultTable1 =
VAR FilterDate1 = SELECTEDVALUE(Messages[MessageDateOnly])
VAR FilterDate2 = SELECTEDVALUE(Messages[MessageDateOnly])
RETURN
FILTER(
Messages,
Messages[MessageDateOnly] = FilterDate1 &&
NOT(Messages[DealerNumberID] IN
CALCULATETABLE(
VALUES(Messages[DealerNumberID]),
Messages[MessageDateOnly] = FilterDate2
)
)
)

ResultTable2 =
VAR FilterDate1 = SELECTEDVALUE(Messages[MessageDateOnly])
VAR FilterDate2 = SELECTEDVALUE(Messages[MessageDateOnly])
RETURN
FILTER(
Messages,
Messages[MessageDateOnly] = FilterDate2 &&
NOT(Messages[DealerNumberID] IN
CALCULATETABLE(
VALUES(Messages[DealerNumberID]),
Messages[MessageDateOnly] = FilterDate1
)
)
)

Any views on this?