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 have one table that contains product information, all unique values:
01-Hammer
02-Screwdriver
03-Wrench
I have two other tables, one that contains customer info
Customer A, 01-Hammer, 5 orders
Customer B, 03-Wrench, 2 orders
Customer C, 03-Wrench, 14 orders
Customer A, 02-Screwdriver, 20 orders
and another that contains external market data :
CityA, 01-Hammer, 80 orders
CityB, 01-Hammer, 35 orders
CityA, 02-Screwdriver, 130 orders
City B, 02-Screwdriver, 70 orders
Both of these tables connect to my first table in a many to one relationship. I pull these last two tables together to get our data vs market data:
01-Hammer, 200 orders (our company), 1500 orders (market)
02-Screwdriver, 100 orders (our company), 1000 orders (market)
03-Wrench, 100 orders (our company), 500 orders (market)
And created a measure for our data and market data to calculate percentage of total products, as well as the difference:
01-Hammer. 200 orders (our company), 50%, 1500 orders (market), 20%, 30%
02-Screwdriver, 100 orders (our company), 25%, 3000 orders (market), 30%, -5%
03-Wrench, 100 orders (our company), 25%, 5000 orders (market), 50%, -25%
The measures I used:
Measure1 = divide(CALCULATE(Count(OurData[ID])),calculate(COUNT(OurData[ID]),all('OurData'[ProductInfo])))
Measure2 = divide(CALCULATE(Count(Market[ID])),calculate(COUNT(Market[ID]),all('Market'[ProductInfoField])))
Measure3 = Measure1 - Measure2
Now, it pulls the correct values where the two ProductInfo match up, but given it's essentially a many to many relationship, the output is not what we want. In the above example, istead of the 3 rows that I want returned, it returns 9:
01-Hammer, 01-Hammer (correct info)
01-Hammer, 02-Screwdriver
01-Hammer, 03-Wrench
02-Screwdriver, 01-Hammer
02-Screwdriver, 02-Screwdriver (correct info)
02-Screwdriver, 03-Wrench
03-Wrench, 01-Hammer
03-Wrench, 02-Screwdriver
03-Wrench, 03-Wrench (correct info)
So, is there some way I can add a filter so only the matching values are returned? Basically an inner join through the visual's filter box? Thanks for reading!
Solved! Go to Solution.
Hi @MarkJ09,
For many to many relationship tables, you can't direct drag then into same visual, you need to drag bridge table column(Product[ID]) to link help power bi analysis columns.
In addition, I'd like to suggest you modify a formula to use bridge table column to filter on related tables:
Measure1 = DIVIDE ( CALCULATE ( COUNT ( OurData[ID] ), VALUES ( Product[ID] ) ), CALCULATE ( COUNT ( OurData[ID] ), ALLSELECTED ( 'OurData' ) ) ) Measure2 = DIVIDE ( CALCULATE ( COUNT ( Market[ID] ), VALUES ( Product[ID] ) ), CALCULATE ( COUNT ( Market[ID] ), ALLSELECTED ( 'Market' ) ) )
If above not help, please share a sample pbix file to test and coding formula.
Notice: do mask on sensitive data and upload it to onedrive or google drive, then share link here.
Regards,
Xiaoxin Sheng
Hi @MarkJ09,
For many to many relationship tables, you can't direct drag then into same visual, you need to drag bridge table column(Product[ID]) to link help power bi analysis columns.
In addition, I'd like to suggest you modify a formula to use bridge table column to filter on related tables:
Measure1 = DIVIDE ( CALCULATE ( COUNT ( OurData[ID] ), VALUES ( Product[ID] ) ), CALCULATE ( COUNT ( OurData[ID] ), ALLSELECTED ( 'OurData' ) ) ) Measure2 = DIVIDE ( CALCULATE ( COUNT ( Market[ID] ), VALUES ( Product[ID] ) ), CALCULATE ( COUNT ( Market[ID] ), ALLSELECTED ( 'Market' ) ) )
If above not help, please share a sample pbix file to test and coding formula.
Notice: do mask on sensitive data and upload it to onedrive or google drive, then share link here.
Regards,
Xiaoxin Sheng
The new measures did the trick. Thank you!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |