topic Re: USING THE EXCEPT & CONCATENATEX FUNCTIONS TO OUTPUT A LIST OF EMPLOYEE NAMES in DAX Commands and Tips

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

kmfitzy — Sun, 14 Jul 2024 00:59:18 GMT

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

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

Anonymous — Mon, 15 Jul 2024 04:38:50 GMT

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

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

kmfitzy — Mon, 15 Jul 2024 06:56:34 GMT

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

Regards,

Karin

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

Anonymous — Mon, 15 Jul 2024 09:52:16 GMT

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.

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

kmfitzy — Mon, 15 Jul 2024 23:44:49 GMT

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

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

Anonymous — Tue, 16 Jul 2024 07:02:04 GMT

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.

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

Anonymous — Fri, 19 Jul 2024 01:04:48 GMT

Hi Anonymous ,
Thanks so much for your proposed solution it was very insightful and I will try to incoporate it into my Dashboard.