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 everyone,
I have the following tables and columns:
1. "Compare Data" table:
Name | Model | Material No | Group | Cost |
A | W5 | 1005-01 | EEC | 100 |
A | W5 | 1005-02 | EEC | 150 |
A | W3 | 1005-03 | DIVIDER | 105 |
A | W3 | 1005-04 | DIVIDER | 3000 |
A | W5 | 1005-05 | EEC | 1215 |
2. "Full Data" table
Vendor Name | Engine Model | Material Number | Material Group | Repair Cost |
A | W5 | 1005-01 | EEC | 80 |
B | W5 | 1005-02 | EEC | 85 |
C | W3 | 1005-03 | DIVIDER | 135 |
D | W3 | 1005-04 | DIVIDER | 3005 |
E | W5 | 1005-05 | EEC | 1115 |
F | W2 | 1005-08 | EEC | 250 |
A | W5 | 1005-09 | EEC | 350 |
A | W5 | 1005-03 | EEC | 135 |
A | W5 | 1005-25 | DIVIDER | 125 |
B | W3 | 1005-2 | DIVIDER | 125.5 |
B | W15 | 1005-3 | EEC | 135 |
R | W5 | 1005-2 | EEC | 450 |
"Compare Data" table only has 1 vendor name while "Full Data" table has all names.
My questions are:
1. I'm looking to find and count all alternatives to A from the "Full Data" table who are capable of repairing a certain model (in a sense that if I use "Model" column as a slicer and one or more selections are made). In other words, other than A, what are the other vendors from the "Full Data" table that can repair the W5, W3, W15,... model for example?
Desired Result: For W5, vendors different from A = E, B, R . I'd like to exclude A from the result as I only want to see any vendors other than A.
2. Is it possible to show A's repair cost of a model using data from the "Compare Data" table and those of alternative vendors, all displaying in a Bubble Chart and at the same time the chart reponses to a slicer that users can choose whichever models or material groups they prefer?
Could you please show me how to do these? Thank you so much for your helps!
Solved! Go to Solution.
@Anonymous@trdoan
sorry for late reply. I had been travelling
Please try this Revised MEASURE and see file attached as well
Measure = VAR mymodel = VALUES ( 'Compare Data'[Model] ) VAR mygroup = VALUES ( 'Compare Data'[Group] ) RETURN CONCATENATEX ( EXCEPT ( CALCULATETABLE ( VALUES ( 'Full Data'[Vendor Name] ), FILTER ( 'Full Data', 'Full Data'[Engine Model] IN mymodel && 'Full Data'[Material Group] IN mygroup ) ), VALUES ( 'Compare Data'[Name] ) ), [Vendor Name], ", " )
For Highest Cost Vendor,,use this MEASURE
Highest cost vendor = VAR mymodel = VALUES ( 'Compare Data'[Model] ) VAR mygroup = VALUES ( 'Compare Data'[Group] ) RETURN CONCATENATEX ( TOPN ( 1, EXCEPT ( CALCULATETABLE ( VALUES ( 'Full Data'[Vendor Name] ), FILTER ( 'Full Data', 'Full Data'[Engine Model] IN mymodel && 'Full Data'[Material Group] IN mygroup ) ), VALUES ( 'Compare Data'[Name] ) ), CALCULATE ( SUM ( 'Full Data'[Repair Cost] ) ), DESC ), [Vendor Name], ", " )
For lowest cost vendor
Lowest cost vendor = VAR mymodel = VALUES ( 'Compare Data'[Model] ) VAR mygroup = VALUES ( 'Compare Data'[Group] ) RETURN CONCATENATEX ( TOPN ( 1, EXCEPT ( CALCULATETABLE ( VALUES ( 'Full Data'[Vendor Name] ), FILTER ( 'Full Data', 'Full Data'[Engine Model] IN mymodel && 'Full Data'[Material Group] IN mygroup ) ), VALUES ( 'Compare Data'[Name] ) ), CALCULATE ( SUM ( 'Full Data'[Repair Cost] ) ), ASC ), [Vendor Name], ", " )
you can use this MEASURE
See file attached
Measure = VAR mymodel = VALUES ( 'Compare Data'[Model] ) RETURN CONCATENATEX ( EXCEPT ( CALCULATETABLE ( VALUES ( 'Full Data'[Vendor Name] ), 'Full Data'[Engine Model] IN mymodel ), VALUES ( 'Compare Data'[Name] ) ), [Vendor Name], ", " )
Thanks for your help! It works great! However, it seems to only response to "Model" slicer but not both "Model" & "Group" slicers? Is it possible to make it work out for both?
I also have 2 others question:
1. How would you show the highest and the lowest-cost vendors using 2 Card Visuals. Like creating 2 measures where they can show who is the highest-cost vendor and same for lowest-cost vendor?
2. Having known which suppliers are capable of repairing a certain engine (by using your measure), can you please also help me create something to use for a Bubble Chart where it can show A's Average Cost and those of alternative suppliers?
I can't seem to get the measure to work in the Bubble Chart and I think my case needs a calculated column of some sort?
Do you know how I can get it done?
Thanks again for your help!
@Anonymous@trdoan
sorry for late reply. I had been travelling
Please try this Revised MEASURE and see file attached as well
Measure = VAR mymodel = VALUES ( 'Compare Data'[Model] ) VAR mygroup = VALUES ( 'Compare Data'[Group] ) RETURN CONCATENATEX ( EXCEPT ( CALCULATETABLE ( VALUES ( 'Full Data'[Vendor Name] ), FILTER ( 'Full Data', 'Full Data'[Engine Model] IN mymodel && 'Full Data'[Material Group] IN mygroup ) ), VALUES ( 'Compare Data'[Name] ) ), [Vendor Name], ", " )
For Highest Cost Vendor,,use this MEASURE
Highest cost vendor = VAR mymodel = VALUES ( 'Compare Data'[Model] ) VAR mygroup = VALUES ( 'Compare Data'[Group] ) RETURN CONCATENATEX ( TOPN ( 1, EXCEPT ( CALCULATETABLE ( VALUES ( 'Full Data'[Vendor Name] ), FILTER ( 'Full Data', 'Full Data'[Engine Model] IN mymodel && 'Full Data'[Material Group] IN mygroup ) ), VALUES ( 'Compare Data'[Name] ) ), CALCULATE ( SUM ( 'Full Data'[Repair Cost] ) ), DESC ), [Vendor Name], ", " )
For lowest cost vendor
Lowest cost vendor = VAR mymodel = VALUES ( 'Compare Data'[Model] ) VAR mygroup = VALUES ( 'Compare Data'[Group] ) RETURN CONCATENATEX ( TOPN ( 1, EXCEPT ( CALCULATETABLE ( VALUES ( 'Full Data'[Vendor Name] ), FILTER ( 'Full Data', 'Full Data'[Engine Model] IN mymodel && 'Full Data'[Material Group] IN mygroup ) ), VALUES ( 'Compare Data'[Name] ) ), CALCULATE ( SUM ( 'Full Data'[Repair Cost] ) ), ASC ), [Vendor Name], ", " )
Hi @Zubair_Muhammad , thank you thank you thank you! They worked perfectly! Only that I had to change the SUM into AVG and && to OR.
BUT THANK YOU SO MUCH!!!
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |