How to count occurence of a name in a column that is chosen from a filter from another column

[Anonymous]

Hi! I'm struggling to find a way to solve this problem, and would really appreciate all help I can get. 

Let's say I have a df with 4 columns:  'Project Name', 'Owner', 'First supplier' and 'Companies'. Both 'Owner', 'First supplier' and 'Companies' contain company names. I Use Companies as a slicer. 

On my desktop, if I choose Company 1 from the 'Companies' slicer, my goal is to visualize the count of occurrences of Company 1 in 'First Supplier' column. It's not given that Company 1 occurs in 'First Supplier' (Then occurrence should be 0), and I have a large data set (So I need a dynamic solution). But all companies that occur in 'First supplier' are in the 'Company' column. The columns are in the same data frame, connected by an ID called 'Project Name'. 

Example:

Project Name	Owner	First supplier
Project 1	Company 1	Company 20
Project 2	Company 2	Company 20
Project 3	Company 3	Company 1
Project 4	Company 1	Company 20

When filtering on company 1, the table looks like this:

Project Name	Owner	First supplier
Project 1	Company 1	Company 20
Project 3	Company 3	Company 1
Project 4	Company 1	Company 20

My goal is to count Company 1 one time in 'First supplier' by a measure and visualize it by a Card. But when I try, it counts two from 'First supplier' : Company 20 and Company 1. 

Does anyone have any suggestion? 

Thank you

best, Hanne

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can create a measure to count the occurence of a selected company like below and put it into a card visual. 

occurence =
COUNTROWS (
    FILTER (
        ALL ( 'table' ),
        'table'[First supplier] = SELECTEDVALUE ( 'table'[Companies] )
    )
)

If you also want the table visual to be filtered like the second table you show, you need to have a disconnected table containing all possible company names and use it in the slicer. Then create a measure to help filter the table visual. If you need this result, please let us know.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[lbendlin]

"The columns are in the same data frame, connected by an ID called 'Project Name'"

Not sure what you mean by data frame  (data model maybe?)  but this is your issue.  For this request to have any chance of succeeding you need to use a disconnected table that has all possible company names (across all columns of your original tables) , that disconnected table then needs to feed your search slicer, and then you need to create measures that take the slicer selection and compare it to the individual columns. 

There are custom search visuals that allow to search across multiple fields. Might be a better idea?