Get all Company name based on Industry Name

[mohittimpus]

Hello All,

I have 2 table:

1) Company

2) Employee

I have a filter of company name in page1 tab. I want to fatch the All company name based on Industry name.

one filter is there of company name which is taking by company table.

industry name i am facthing with respect company name with below measure:

Get Industry by company =

var val = SELECTEDVALUE(Companies[company name])
var industry = IF(val<>BLANK(), VALUES(CreditorWatch[ANZSIC Industry]))
return industry

here I am getting Industry name using above measure. 

Now, in below image one Industry has 2 company name.

there are 2 company name in same industry "K_Finance and insurance services".

I want this 2 company name for industry "K_Finance and insurance services". in a measure.

I used below measure:

var val = SELECTEDVALUE(Companies[company name])
var industry = IF(val<>BLANK(), VALUES(employee[Industry]))
return industry

I got industry name by above measure.

after that i used below measure to get company name but i am getting single company name, but it must take 2 company name for industry "K_Finance and insurance services". 

All Company Name = IF([Get Industry by company]<>BLANK(), VALUES(employee[Name]))

above measure is showing only 1 company. 

below file is attached Excel and .pbix file. 

power bi file:

https://drive.google.com/file/d/1E1F8iqU5ysCrz8dzaZAwXnsn6kuti5lG/view?usp=sharing

Excel sheet image

[v-jingzhang]

Hi @mohittimpus ,

A measure is not able to express multiple values at the same time. It is usually an aggregate value. So in this case, you can use CONCATENATEX function to combine all the values into one single string value and then display it in the visual. Try the measure below, here I use “; ” as the delimiter between values.

All Company Name =
VAR __industry = SELECTEDVALUE ( employee[Industry] )
VAR __table = FILTER ( ALL ( employee ), employee[Industry] = __industry )
VAR __table2 = SUMMARIZE ( __table, employee[Company Name] )
RETURN
    CONCATENATEX ( __table2, employee[Company Name], "; " )

Moreover, if you want to display multiple company names in multiple rows of a table visual, here is another way you can take which needs a few steps to follow:

1. Create a Company List table

CompanyList = VALUES(Companies[company name])

2. Create a measure

Measure =
VAR __industry = SELECTEDVALUE ( employee[Industry] )
VAR __table = FILTER ( ALL ( employee ), employee[Industry] = __industry )
VAR __table2 = SUMMARIZE ( __table, employee[Company Name] )
RETURN
    IF ( MAX ( CompanyList[Company Name] ) IN __table2, 1, 0 )

3. Put CompanyList[company name] column into the table visual and add above measure to the table’s filter.

Best Regards,

Community Support Team _ Jing Zhang

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

[AllisonKennedy]

To get two company names in single measure, you need to use CONCATENATEX to combine multiple rows into 1 value.
You'll also have to make sure you put it in the right visual, because if you put it by company name, you'll only see one company name.