Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
magnusks
Helper I
Helper I

Combine 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

  
ProjectProjectKey EmplDep EmplProjectKeyHours
137281 18a 18153
108672 15b 15287
166513 26c 26390
100384 91d 91453
186435 13e 13584
185586 90f 90697
156827 38g 38796
147188 10h 10879
148969 80i 80952
1978410 2j 21070


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?

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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

 

vyangliumsft_0-1709273044538.png

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:

 

vyangliumsft_2-1709273094957.png

 

 

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

View solution in original post

8 REPLIES 8
v-yangliu-msft
Community Support
Community Support

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

 

vyangliumsft_0-1709273044538.png

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:

 

vyangliumsft_2-1709273094957.png

 

 

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

@v-yangliu-msft ,

 

Thank you. That worked! 🙂

govindarajan_d
Solution Supplier
Solution Supplier

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

 

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?

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

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

Hi @govindarajan_d ,

 

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

magnusks_0-1709231978901.png

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. 

Hi @magnusks ,

 

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

govindarajan_d_0-1709261679216.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors