Forum Discussion
Using a specific value from an unrelated table
I've been using Power Bi for about 6 months so fairly new and this one has me stumped. I've searched the forums and just can't wrap my mind around how to apply it to my scenario.
I have a variety of tables that are unrelated. Here are my main tables:
I need to do a count of AgileDataEntryQueueID from the Pre-Pro_DB table by Automation_Month and by Capacity_Plan.
Once I have that count I want to multiply it by the LocationRatio in the ReceiptsLocationRatio table. This is where I get screwed up.
In the RecieptsLocationRatio there are multiple geographic locations and a specific [Location Ratio] associated with the location and date (which is the same as the Automation_Month). What I'm trying to do is get the count of AgileDataEntryQueueID associated with the specific location and date from the ReceiptsLocationRatio table using the [Location Ratio] values.
For example the the count = 40 and the location ratio for Ireland is .25, Domestic is .30, India is .10 and Philippines is .35. I want to create either a table or a measure that will tell me the count for ireland =10, Domestic=12, India=4 and Philippines is 14.
Once I get that count I will have to relate those counts to the next table VPH_ByLocation to divide by the value associated with the location and date of those counts.
Thank you in adance for the help and the patience!
3 Replies
PaulDBrownCommunity Champion
You should be creating dimension tables for all the fields which are common to the tables and use them in slicers, filters, measures and visuals.
Is there a main date field in the Pre-Pro_DB table? Are there any other common fields between tables?The common fields between the 3 tables are Automation_Month, Date, Capacity plan and location. My intention is create either a calculated column or measure for the Pre-Pro_DB table that pulls in the [Location Ratio] for a specific date and capacity plan from ReceiptsLocationRatio table.
In the screen shots below I have the Pre-Pro_DB table showing the Automation_Count.
I want to first match up the Automation_Month from the Pre-Pro_DB table to the Date on the ReceiptsLocationRatio.
Then I want to match up the Capacity Plan from the Pre-Pro_DB table to the Capacity Plan from the ReceiptsLocationRatio.
Then I want to specify the Location (i.e. Ireland) in the ReceiptsLocationRatio table.
Then I want to take the LocationRatio value that matches the above from the ReceiptsLocationRatio table and multiply it by the Automation_Count from the Pre-Pro_DB table.
Once I have all that I will need to do the same process for a different calculated column/measure using the VPH_ByLocation table.
In excel it would be a simple process of xlookups or index but I can't figure it out here 😞
- PaulDBrown
Any chance you can share some dummy data for all 3 tables or a link to a dummy PBIX file?
