Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
"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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |