Create a report from two Lists-with multiple text column lookup field

[Anonymous]

Trying to generate a report, in power bi, to list Managers who have resigned, the company and other Managers from 2 lists:

Companies List:  CompanyID, Company Name, Managers Name(lookup from Managers List-multiple values field-can be 1, 2, 3 or 4)

Managers List: ManagerID, Name of the Manager(unique), Status(Active or Resigned)

If the status in the “Managers List” is “Resigned”, then List the name of the company from the “Companies List”, the name of the Manager from the “Managers List” and all Managers Name(may be 1,2, 3 or 4) from the Companies List along with him. 

Thanks.

[Anonymous]

Thanks Allan, 

it works fine now... thanks:)

[v-alq-msft]

Hi, @Anonymous 

Based on your descripton, I created data to reproduce your scenario. The pbix file is attached in the end.

Manager:

Company:

You may create a measure as below.

Result Status = 
IF(
    CONTAINSSTRINGEXACT(MAX(Company[BoardManagers]),MAX(Manager[ManagerName]))&&MAX(Manager[Status])="Resigned",
    "Resigned",
    "Active"
)

Result:

Best Regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

Thanks Allan for your efforts, however, I am not getting the same results as you, may be due to the relationship issue. 

The ManagerName field in Manager is unique name of the person and the BoardManagers field in Company is a multiple text field and that too is a Lookup to ManagerName field in Manager List.

Appreciate your comments.

[v-alq-msft]

Hi, @Anonymous 

Could you please show us some sample data about two tables and the expected result? What is the relationship between two tables? Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

[Anonymous]

Dear Allan, Thanks for your efforts, I copy the screenshot as required:

Can you suggest the relationship here, as I am not sure about the relationship b/w these two files? Ideally it should be 'Company List'[BoardManagers] and 'Manager List'[ManagerName], but I think it is a data type mismatch here b/w multiple text field and single text field.

The expected output is to list all Managers(Resigned) only, once we click on any Managers(Resigned), the next two column should list the CompanyName and the ManagerName, for example if we click Mgr7, it should list the Company and ManagerName on the next column

Kind regards.

Laik

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

I'd like to suggest you create a calculated table and a measure as below. There is no relationship between tables. The pbix file is attached in the end.
Calculated table:

Table = 
CALCULATETABLE(
    DISTINCT('Manager List'[ManagerName]),
    FILTER(
        ALL('Manager List'),
        [Status]="Resigned"
    )
)

Measure:

Visual Control = 
var c = 
COUNTROWS(
    FILTER(
        DISTINCT('Table'[ManagerName]),
        CONTAINSSTRINGEXACT(MAX('Company List'[Board Managers]),[ManagerName])
    )
)+0
return
IF(
    c>0,
    1,0
)

Then you need to put the measure in the visual level filter and use 'ManagerName' from 'Table' to filter the result.

Best Regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[DavisBI]

Hi, @Anonymous 

Can you provide a screenshot to show the result you want to achieve?

---

[Anonymous]

Thanks Davis for your response.

Attached is a screen shot, hope that makes it clear, please let me know should you need to know more info.