Forum Discussion
Helper ICombine concatenatex and selectedvalue
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?
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
8 Replies
- govindarajan_d
Super User
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")
magnusksHi 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
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.
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
Anonymous ,
Thank you. That worked! 🙂
