Multiple relationships issue and help required

[Snowy34]

Good day all,

I have been building a simple dashboard I have a Master Locations table that holds all locations for each warehouse, the report will basically look at all used locations within CUR0015 report and give us some information.

The problem I got is Master Location table has sites and locations that I would like to link to CUR-0015 report and right now I only have locations linked and I can't get the site to link up meaning my visuals are all wrong.

This is my working report that does not have the SITE functions hooked up and the data in CUR-0015 only has one site in it so everything calculates fine

Below is what happens when I include different site data within CUR0015 and Master Locations. The visual is all over the place and adding a slicer doesn't fix the issue 😞 

Any help with this would be great as I would like to have each site on one visual dashboard but if this can't be done then 5 different site-specific dashboards will have to do.

Regards 

[vanessafvg]

@Snowy34  finding it a little difficult to understand what you have here, can you show the table structure and relationships and demonstrate what you expecting?

[Snowy34]

Good day @vanessafvg 

So the Location count by type in the second screenshot is wrong, it's counting all sites when it should only count Site 400 in this case as per screenshot 1 even when I add a Site Slicer. Without the slicer, it just adds everything together and ever is right but its, when I try to check each individual sites the Location Count by Type, is right but the rest is all wrong.

I'm using this to get my location count 

Location Count by Type

Prime Shelf =

CALCULATE(COUNTA('Master Locations'[Type]), 'Master Locations'[Type] IN { "Prime - Shelf" })

Used Locations by Type

Used Prime Shelf =

CALCULATE(

    COUNTA('CUR-0015'[location]),

    'Master Locations'[Type] IN { "Prime - Shelf" }

)

Free Locations by Type

Free Prime Shelfs =

[Prime Shelf] - [Used Prime Shelf]

relationships as requested, that inactive relationships from Master Location to CUR is site relationships but it's not active and I can't get it to work without that my guess is.

[vanessafvg · Accepted solution]

@Snowy34  is there any chance you can share the data, i find it easier to work it out if its in front of me, but i think you might need to use a filter rather than in

try this

Used Prime Shelf =
CALCULATE (
    COUNTA ( 'CUR-0015'[location] ),
    FILTER ( 'Master Locations', 'Master Locations'[Type] = "Prime - Shelf" )
)