USING THE EXCEPT & CONCATENATEX FUNCTIONS TO OUTPUT A LIST OF EMPLOYEE NAMES

[kmfitzy]

Hi,
The Matrix below shows a count of employees by reporting week.
I would like to be able to output a list of employee names when comparing 2 reporting weeks to identify what names appear in one week but not the other week. Ideally I would like to output this list via a drillthrough. I have tried using a combination of CONCATENATEX & EXCEPT as suggested by @Greg_Deckler on a diferent thread, however, I haven't been successul in getting it to work. It makes sense to me that those functions would be part of the solution.
Please refer to the screenshots below. 
Any advice would be greatly appreciated. 🤗
Regards,
Karin
Sample PBIX: https://drive.google.com/file/d/18igYaWq6okSlcbtWHAbmhFMWVU3ZpO_6/view?usp=drive_link

	Desired Output of Drillthrough
	Desired Output of Drillthrough

[v-linyulu-msft · Accepted solution]

HI,@kmfitzy 

Regarding the issue you raised, my solution is as follows:

1.In the matrix you provided, if you want to drill through to a specific page on a specific date, it is not possible at present, in the PBIX file I provided, we can only manually select the page you need to drill through after selecting the date, as shown in the following figure:

2Second, you can try inserting a page navigation as shown in the following image:

Create page and bookmark navigators - Power BI | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[v-linyulu-msft]

HI,@kmfitzy 

Thank you for your detailed description of your problem, but if you need more specific help, we hope you can use sharepoint or OneDrive to share your pbix files, as we don't have access to Googledrive files due to our security protocols.

Of course, if you have already found a solution or made a new discovery, you are welcome to share it with us.

Thank you for your understanding.

Best Regards,

Leroy Lu

[kmfitzy]

Hi @v-linyulu-msft,
Thanks for your reply. Please see if this link works for you: Employee List Differences.pbix.

Regards,

Karin

[v-linyulu-msft]

HI,@kmfitzy 

Regarding the issue you raised, my solution is as follows:

1. Below are the measure I've created for your needs:

MEASURE = 
VAR p2107l =
    SELECTCOLUMNS (
        FILTER ( 'Employees', 'Employees'[REPORTINGWEEK] = DATE ( 2023, 7, 14 ) ),
        "1", 'Employees'[EMPLOYEE]
    )
VAR c1407 =
    SELECTCOLUMNS (
        FILTER ( 'Employees', 'Employees'[REPORTINGWEEK] = DATE ( 2023, 7, 21 ) ),
        "2", 'Employees'[EMPLOYEE]
    )
VAR inx =
    INTERSECT ( p2107l, c1407 )
VAR res =
    UNION ( EXCEPT ( p2107l, inx ), EXCEPT ( c1407, inx ) )
RETURN
    IF ( MAX ( 'Employees'[EMPLOYEE] ) IN res, 1, 0 )

Measure2 = 
VAR p2807 =
    SELECTCOLUMNS (
        FILTER ( 'Employees', 'Employees'[REPORTINGWEEK] = DATE ( 2023, 7, 21 ) ),
        "1", 'Employees'[EMPLOYEE]
    )
VAR c2107 =
    SELECTCOLUMNS (
        FILTER ( 'Employees', 'Employees'[REPORTINGWEEK] = DATE ( 2023, 7, 28 ) ),
        "2", 'Employees'[EMPLOYEE]
    )
VAR inx1 =
    INTERSECT ( p2807, c2107 )
VAR res1 =
    UNION ( EXCEPT ( p2807, inx1 ), EXCEPT ( c2107, inx1 ) )
RETURN
    IF ( MAX ( 'Employees'[EMPLOYEE] ) IN res1, 1, 0 )

2.Then modify the visualization's settings:

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[kmfitzy]

Hi,
Thank you very much for providing me with your solution it is so very close. It has really shown me ways to use the UNION & INTERSECT functions.

However, when I right-click and drillthough on the reporting week = 28/07/2023 it passes the reportingweek and doesn't give me the correct list. If I remove the reportingweek from the list of drillthough fields then I get Emp 6. The same applies when I right-click on reportingweek = 14/07/2023. How did you manage to stop the reportingweek from being passed to the Drillthrough page?
If we could suppress the reportingweek being passed to the drillthrough page it would work.

Also, I want to be able to drillthrough onto one page only.

Any advice on how I could resolve this, as it is so close, would be fantastic?

Regards,

Karin

[v-linyulu-msft · Accepted solution]

HI,@kmfitzy 

Regarding the issue you raised, my solution is as follows:

1.In the matrix you provided, if you want to drill through to a specific page on a specific date, it is not possible at present, in the PBIX file I provided, we can only manually select the page you need to drill through after selecting the date, as shown in the following figure:

2Second, you can try inserting a page navigation as shown in the following image:

Create page and bookmark navigators - Power BI | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[KMF2022 · Accepted solution]

Hi @v-linyulu-msft ,
Thanks so much for your proposed solution it was very insightful and I will try to incoporate it into my Dashboard.