Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

If/And Measure? Or Filter?

Hello All,

I have one column of Projects, two columns of Names, and two columns of Pay in my data set.

 

Column A = Project Name

Column B = Lead Person

Column C = Support Person

Column D = Lead Pay

Column E = Support Pay

 

I'd like to write a measure that finds someone's name in both Column B and Column C and sums the appropriate Pay from Columns D & E. 

 

Example

Project ABC     Bob    Sam     $10     $20

Project XYZ     Tim     Tom     $15     $30

Project GEF     Sam    Tim      $50     $75

 

If I search for Tim, the sum of the pay should be $90 (15 + 75). Additionally, if I filter my table view I'd like to see only the projects that Tim shows up in either Column B or Column C. 

 

Any help is appreciated!

 

 

Need Help

5 Replies

  • vicky_'s avatar
    vicky_
    Super User
    3 years ago

    Here's my attempt at solving that problem - 
    I first created another table with the names of all the staff - 

    All Staff = DISTINCT(UNION(VALUES('Table (2)'[Lead]), VALUES('Table (2)'[Support])))

    This table is disconnected from the original table.

    And then I wrote a measure to grab the sum of the pay for each role: 

    Sum of Pay = 
var summaryTable = UNION(
    SELECTCOLUMNS('Table (2)', "name", 'Table (2)'[Lead], "pay", 'Table (2)'[Lead Pay]), 
    SELECTCOLUMNS('Table (2)', "name", 'Table (2)'[Support], "pay", 'Table (2)'[Support Pay])
)
return SUMX(FILTER(summaryTable, [name] = SELECTEDVALUE('All Staff'[Lead])), [pay])

    I then used the names from the table I created, and then used the measure to create the results table.

     

    Lastly, for the table filtering requirements, I recommend you take a look at this article that goes more in-depth on slicer filtering: https://apexinsights.net/blog/or-xor-slicing

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello vicky_ ,

      Thank you for the detailed reply!

       

      I follow most of what you've laid out but have a couple questions. 

       

      1. I already have a separate table with each of the peoples names in it (listed once each). So I think I can skip your first step, correct?
        1. I do not have unique lists of names for Lead and Support. There is one source of names, and those can either be populated in Lead or Support in the data source. So my name column would apply to both. 
      2. For the Measure "Sum of Pay" is that in my main table or in the one you created separately?
        1. You mentioned that your new table is disconnected so I'm wondering if I'm just not putting the measure in the right place. Right now I created the measure in my original table. 
        2. When I use the values in a table it is not adding them correctly.
          1. My assumption is that something I've done with the name column is driving this. 
      3. For the filtering link you sent me I guess I have the same question about creating separate tables. Since I already have a separate table with a single list of names in it, do I need to duplicate that table and reference both tables as shown in your link?

      Thank you again for the help on this!

       

       

      • vicky_'s avatar
        vicky_
        Super User
        3 years ago

        Hey!

         

        1. Yes, if you already have a list of staff, there's no need to create a new table for that. Just use the name in that table instead of the AllStaff Table I have above. The point of me creating a table with unique names is so that I can grab the pay regardless of if the staff is lead or support. 

        2. Measures (as opposed to calculated columns) don't depend on the table that they're in, but rather the row and column dimension(s) that are in the visual. So actually, it doesn't matter which table you put the measure in. (Actually, before power BI implemented folders, some people would create an empty table to use as a folder to organise their measures)

        3. And again, if you already have a list of names, then you don't need to create a new table for that. Just use the one you already have.

         

        Hope that helps.