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

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.

8 Replies

  • DavisBI's avatar
    DavisBI
    Icon for Solution Specialist rankSolution Specialist
    5 years ago

    Hi, Anonymous 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

       

       

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

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

    Thanks Allan, 

    it works fine now... thanks:)