topic Re: Combine concatenatex and selectedvalue in DAX Commands and Tips

Combine concatenatex and selectedvalue

magnusks — Thu, 29 Feb 2024 09:31:36 GMT

Hi all.

I have a table showing workhours per employee. Each employee might work on various projects and at different departments at the company. I have a factstbl where the workhours are stored, and two dimtbls where one has info regarding projects and the other regarding departments (plus some other stuff).

Table name: DimProjects		Table name: DimEmpl		Table name: Facts
Project	ProjectKey	Empl	Dep	Empl	ProjectKey	Hours
13728	1	18	a	18	1	53
10867	2	15	b	15	2	87
16651	3	26	c	26	3	90
10038	4	91	d	91	4	53
18643	5	13	e	13	5	84
18558	6	90	f	90	6	97
15682	7	38	g	38	7	96
14718	8	10	h	10	8	79
14896	9	80	i	80	9	52
19784	10	2	j	2	10	70

I have a stacked coloumnchart that shows the amount of hours worked per department per year (have a datetable too). In addition to this I want to show as a tooltip the projects that the employees for each department has worked on.

I have tried to create a measure for this (below). If I put the measure into a table I get the "NA" as expected when I dont filter one department. But as soon as I filter on department the table crashes. In addition, the measure causes the barchart to "crash" when I place it as a tooltip.

List of projects = var _conc = CONCATENATEX( DimProjects,DimProjects[Project]) var _selected = SELECTEDVALUE(DimEmpl[Dep]) var _result = IF(_selected, _conc, "NA") RETURN _result

I cant wrap my head around where Im going wrong. Any suggestions?

Re: Combine concatenatex and selectedvalue

govindarajan_d — Thu, 29 Feb 2024 12:41:43 GMT

Hi magnusks ,

I see that if condition does not have any condition in the first parameter. Can you use the following code where we check the selected value is equal to the concatenated value?

var _result = IF(_selected = _conc, _conc, "NA")

Re: Combine concatenatex and selectedvalue

magnusks — Thu, 29 Feb 2024 13:33:13 GMT

Hi govindarajan_d ,

Thanks for the quick reply.

I swapped the "var _result" with the code you suggested, when I did the measure returns all projects. Not sure if that is what you wanted to know?

Re: Combine concatenatex and selectedvalue

magnusks — Thu, 29 Feb 2024 13:35:51 GMT

govindarajan_d ,

Sorry it seems I was a little bit to quick to paste in your measure. The measure now returns NA, not the list of all projects.

Re: Combine concatenatex and selectedvalue

govindarajan_d — Thu, 29 Feb 2024 16:46:31 GMT

Hi magnusks,

I understood your question wrongly. Sorry about that. Can you please try this:

List of projects = var _conc = CONCATENATEX( DimProjects,DimProjects[Project]) var _selected = SELECTEDVALUE(DimEmpl[Dep], "All") var _result = IF(_selected <> "All", _conc, "NA") RETURN _result

Re: Combine concatenatex and selectedvalue

magnusks — Thu, 29 Feb 2024 18:46:03 GMT

Hi govindarajan_d ,

Thanks for the suggestion. See below for a table showing the result.

What I expected / wanted is the table to only show the project that has an employee in the corresponding department.

Maybe there is another way to go about this? I want to add "projects" as a tooltip for my barchart, but it only shows the "first" in the list of projects, and I want to show projects related to the department.

Re: Combine concatenatex and selectedvalue

govindarajan_d — Fri, 01 Mar 2024 02:55:07 GMT

Hi magnusks ,

I am getting only the corresponding projects for each department using the same measure. Can you check once?

Re: Combine concatenatex and selectedvalue

Anonymous — Fri, 01 Mar 2024 06:07:55 GMT

Hi magnusks ,

Here's another idea, you can also try the following.

1. Create calculated table -- use the columns of the new table as slicers.

Table = DISTINCT('DimEmpl'[Dep])

2. Create measure.

List of projects = var _select=SELECTCOLUMNS('Table',"Dep",'Table'[Dep]) var _empl= SELECTCOLUMNS( FILTER(ALL('DimEmpl'),'DimEmpl'[Dep] in _select),"emp",'DimEmpl'[Empl]) var _projectkey= SELECTCOLUMNS( FILTER(ALL('Facts'),'Facts'[Empl] in _empl),"projectkey",'Facts'[ProjectKey]) var _con= CONCATENATEX( FILTER(ALL('DimProjects'), 'DimProjects'[ProjectKey] in _projectkey),'DimProjects'[Project],"-") return IF( NOT( MAX('DimEmpl'[Dep])) in _select,BLANK(), IF( ISFILTERED('Table'[Dep]),_con, "Na") )

3. Result:

Best Regards,

Liu Yang

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

Re: Combine concatenatex and selectedvalue

magnusks — Fri, 01 Mar 2024 07:02:23 GMT

Anonymous ,

Thank you. That worked! 🙂