Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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 Manager...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
5 years ago

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.