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 have three tables
Table layout and relationships
I want to create a slicer called ‘Work Group’ which is common to Work Centre and Network tables such that when I select a slicer value it will give me the sum of costs column from the Costs table for the related records.
I am having trouble creating a common table called Work Group and then connecting it to Work Centre and Network tables. It will only allow one relationship and hence I am not able to filter the costs for all records from both tables.
Thanks for any suggestions.
Yoshi
Solved! Go to Solution.
Hi @Yoshimitsu411 ,
According to my understanding, you want to calculate the sum based on the matched value in Work Centre table or Network table,right?
Please follow these steps:
1. Add a new table and do not build relationships.
ForSlicer =
DISTINCT (
UNION (
SELECTCOLUMNS ( 'Work Centre', "WorkGroup", [Work Group] ),
SELECTCOLUMNS ( 'Network', "WorkGroup", [Work Group] )
)
)
2. Create a sum measure
Measure =
VAR _all =
ALLSELECTED ( 'ForSlicer'[WorkGroup] )
VAR _wono =
SUMMARIZE (
FILTER ( ALL ( 'Work Centre' ), 'Work Centre'[Work Group] IN _all ),
'Work Centre'[Work Centre No]
)
VAR _neno =
SUMMARIZE (
FILTER ( ALL ( 'Network' ), 'Network'[Work Group] IN _all ),
'Network'[Network No]
)
RETURN
CALCULATE (
SUM ( 'Costs'[Costs] ),
FILTER (
'Costs',
'Costs'[Work Centre No]
IN _wono
|| 'Costs'[Network No] IN _neno
)
)
The final output is shown below:
Here is the pbix file.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Yoshimitsu411 ,
According to my understanding, you want to calculate the sum based on the matched value in Work Centre table or Network table,right?
Please follow these steps:
1. Add a new table and do not build relationships.
ForSlicer =
DISTINCT (
UNION (
SELECTCOLUMNS ( 'Work Centre', "WorkGroup", [Work Group] ),
SELECTCOLUMNS ( 'Network', "WorkGroup", [Work Group] )
)
)
2. Create a sum measure
Measure =
VAR _all =
ALLSELECTED ( 'ForSlicer'[WorkGroup] )
VAR _wono =
SUMMARIZE (
FILTER ( ALL ( 'Work Centre' ), 'Work Centre'[Work Group] IN _all ),
'Work Centre'[Work Centre No]
)
VAR _neno =
SUMMARIZE (
FILTER ( ALL ( 'Network' ), 'Network'[Work Group] IN _all ),
'Network'[Network No]
)
RETURN
CALCULATE (
SUM ( 'Costs'[Costs] ),
FILTER (
'Costs',
'Costs'[Work Centre No]
IN _wono
|| 'Costs'[Network No] IN _neno
)
)
The final output is shown below:
Here is the pbix file.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-eqin-msft
How can I use the Work Group slicer to filter the Costs table given there are two relationships to different tables?
Thank you.
Yoshi
Thanks @v-eqin-msft that was what I was after. Perfect!
Also thanks to @amitchandak for his contribution.
Yoshi
@Yoshimitsu411 , Assume three tables are joined as shown in figure
calculate(sum(Costs[cost]), filter( Network, network[Work Group] in values(Workcenter[work Group])))
[Work Group] should filter cost because of join and then Network will also filter for those work groups because of filter. It is like AND
Thanks @amitchandak for the quick response. I need to create a measure too as part of this solution so your suggestion is very much appreciated.
The report must use a slicer called Work Group.
My data and required output is as follows:
Note: Greyed columns do not exist in data table, I have added it for reference only.
Thanks
@Yoshimitsu411 , can you share sample data of three table and output in table format.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |