Forum Discussion

Snowy34's avatar
Snowy34
Icon for Helper III rankHelper III
7 years ago
Solved

Multiple relationships issue and help required

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 

Need Help
  • vanessafvg's avatar
    vanessafvg
    7 years ago

    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" )
    )

3 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Snowy34
      Icon for Helper III rankHelper III
      7 years ago

      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's avatar
        vanessafvg
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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" )
        )