Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

How to slice data for several columns

Project IDProject Owner NameProject Owner IDManagerManager IDProject Details
AJeff123Mike789etc
BJohn234Matt678etc
CJack345John234etc

 

ManagersManager ID
Mike789
Matt678
John234

I have two tables. One table has Managers and their ID. The other main table has all of my data including two columns with the project owner ID and their manager's ID. Sometimes the manager is also a project owner. In Power BI I am trying to set up a lot of data visuals, with a slicer of different manager to show each manager's projects hour contributions. How do I set up the slicer/Power BI so that the ID column from the manager table is connected to both Project owner and Manager in the main table, so that my slicer of managers when one manager is clicked, all the visuals will update so that they contain project info where the manager is project owner as well as the manager's employees under them. 

Right now I have tried to make two relationships, from Manager ID to Manager ID and Manager ID to Project Owner ID, but Power BI only lets one relationship to be active at once. 

 
 
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    Anonymous 

    do not create relationship between two tables.

    create a measure

    Measure = if(max('Table'[Manager])=max(Manager[Managers])||max(Manager[Managers])=max('Table'[Project Owner Name]),1)
    and add this measure to visual filter and set to 1
     
    pls see the attachment below

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    Anonymous 

    do not create relationship between two tables.

    create a measure

    Measure = if(max('Table'[Manager])=max(Manager[Managers])||max(Manager[Managers])=max('Table'[Project Owner Name]),1)
    and add this measure to visual filter and set to 1
     
    pls see the attachment below
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks for your help. Would this be possible if Project Owners are a measure that concatenates several different project owners if they have the same project ID? I have a separate measure for that, so was wondering if I could do something like "if contains" or something of that sort. 

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        1 year ago

        do you mean multiple seleciton or something else? could you pls update the sample data and expected output?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    These Power steps will allow you to work with slicers

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Project ID","Project Details"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Manager","Manager Name",Replacer.ReplaceValue,{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Designation", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"

    Hope this helps.