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.
Hello,
I am trying to make a table visual of sales data that is filtered by a "sales manager" slicer. I currently have two tables:
These tables are related by product, region and team.
My requirement is that the sales table visual is filtered when a user selects a "sales manager" and then only the sales that correspond to the selected manager's responsibilties are visible. For example, Jim is responsible for Product A and Region Asia, so when Jim is selected, I would like the visual to only show sales that correspond to Product A and Region Asia. I am having trouble because you cannot create more than one relationship between two tables. Currently, when I select Jim my table visual is showing all sales that correspond to Region Asia, regardless of Product, because the only relationship that exists is Region --> Region. How would I go about creating this tri-relationship between two tables?
See below for my desired result as well as my current result, along with a sample dashboard.
Current Result (All Region Asia, regardless of Product):
Desired Result (Region Asia and Product A):
Sample Dashboard: https://www.dropbox.com/s/agmlpph9vnw0oqq/6-16%20Sample.pbix?dl=0
Thank you for your help.
Solved! Go to Solution.
Hi, @Anonymous
You may create a measure as below. There is no relationships between two tables. If you want relationships, you may create another same table to create realtionships. The pbix file is attached in the end.
Visual Control =
IF(
NOT(ISFILTERED(Responsibilities[Manager])),
1,
IF(
SELECTEDVALUE(Sales[Product]) in DISTINCT(Responsibilities[Product])&&
SELECTEDVALUE(Sales[Region]) in DISTINCT(Responsibilities[Region]),
1,0
)
)
Then you need to put the measure in the visual level filter to filter the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
You may create a measure as below. There is no relationships between two tables. If you want relationships, you may create another same table to create realtionships. The pbix file is attached in the end.
Visual Control =
IF(
NOT(ISFILTERED(Responsibilities[Manager])),
1,
IF(
SELECTEDVALUE(Sales[Product]) in DISTINCT(Responsibilities[Product])&&
SELECTEDVALUE(Sales[Region]) in DISTINCT(Responsibilities[Region]),
1,0
)
)
Then you need to put the measure in the visual level filter to filter the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous ,
Concatenate the two-column in both the tables and join
Key = [Region] & "-" & [Team]
and then join. It should work.
Either normalize your data model, or use composite keys (for example a concatenation of sales manager and region)
There might be other solutions too.
Thank you for the reply. Can you elaborate on normalizing my data model? I'd rather not concatenate as the dataset is large and it would require a lot of memory.
Normalization= moving dimension columns (stuff you filter by) out into their own dimension table, leaving only facts (stuff you do math on) and keys (lookups to the dimension) in the facts table.
Anything with low cardinality is probably a dimension.
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |