Trouble Calculating Sum / Countrow

[a68tbird]

Hello All,

   Having trouble wrapping my head around how to get the number I'm looking for here. Here's the scenario (note I thought about posting some sanitized data to help, but it's millions of rows, multi tables....so I hope my explanation here suffices).

First, the relationships:

Label is related to the mrProducts table on a many-to-one relationship.  (Labels * : 1 mrProducts)

OrderItems table is the sales table, related to mrProducts on a many-to-one relationship (OrderItems * : 1 mrProducts)

OrderItems relates to Campaigns as many-to-one (OrderItems * : 1 Campaigns)

Campaigns relates to Blocks as one-to-many (Campaigns 1 : * Blocks)

Blocks relates to Codes as one-to-many (Blocks 1 : * Codes)

We issue codes used to validate against a product sale. Many codes are issued, only a percentage are redeemed. I'd like to figure out how many codes - on a per label basis - are issued (and from there I can figure out the percentage). The report canvas has a slicer with Labels. Selecting that label shows all the sales no problem, but trying to establish how many codes were issued per campaign is where I get stuck.  The Codes table has all codes that are issued.  Essentially, I need to count the number of rows in Codes that are related to any of the products within the Label found set.  That's a very circuitous route:  Codes -> Blocks -> Campaigns -> OrderItems -> mrProducts -> Label.

Any thoughts? Is it possible? Let me know if I can further simplify this explanation. 

[v-lili6-msft]

hi,@a68tbird 

  After my research, you may try to change Cross filter direction to both when creating the relationship

between Campaigns and OrderItem.

if it is not your case, please share your demo pbix file the expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[a68tbird]

Thanks Lin. What would my calculation be then? Could you help with that?