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

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

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