Combine concatenatex and selectedvalue

[magnusks]

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?

[v-yangliu-msft · Accepted solution]

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

[magnusks]

@v-yangliu-msft ,

Thank you. That worked! 🙂

[govindarajan_d]

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")

[magnusks]

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?

[magnusks]

@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.

[govindarajan_d]

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

[magnusks]

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. 

[govindarajan_d]

Hi @magnusks ,

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