powerbidaxreportpowerbi-desktopdashboard

Writing a Power BI measure to check multiple records in a related table based on values in both tables

I've got my data structured as below in a contractProducts table, gives each combination of contract and product as well as the previous product for each contract (each contract can have more than one product).

contract_id product_id previous_product_id
1 1 1
2 2 1
2 3 3
3 3 2
4 1 3
5 3 1
6 2 3
6 1 2
7 3 3
8 1 1
9 1 2
9 2 3
10 3 1

I've also got a product benefits table (below), showing all the benefits selected for each product.

product_id benefit_name
1 legs
1 arms
1 head
1 back
2 legs
2 arms
2 hands
2 feet
3 legs
3 hands
3 eyes
3 left kidney

These have a many-many relationship between them based on the product id on each, I'd love to be able to refactor it or somehow split it all out to a much more sensible model, but due to the system I'm working with and how the benefits are structured and attached to product information that isn't feasible (I've tried but this is as close as it gets, long story short is that it's not really possible to make a unique list of benefits and/or products in a way that keeps the information correct).

What I want in power Bi is a matrix that shows each contract, which can expand to show all of their product benefits for their current product, and check whether each benefit is new (based on the previous product id):

Picture of the matrix for the sake of making it easier to display, data is a bit different from above but demonstrates same structure

Essentially, I want a measure that finds:

For each contract

        For each product they have

                For each benefit in that product

                        Check if that benefit exists in their previous product and return yes/no

I've started with a measure using SELECTEDVALUE() to find the product id, previous product id, and the specific benefit name, I define each as a variable in the measure to reuse later. When I test each of these values in a matrix they all come out properly alongside the correct contract/product/benefits (i.e. return each SELECTEDVALUE() separately and display them in the matrix, values are correct).

That's where it starts to not work properly. I've tried a lot of options ( TREATAS, CONTAINS, FILTER, CALCULATETABLE etc.) but nothing seems to want to give me what I expect. I've managed to get partway there with GENERATE, generating a table from contract product and a filtered version of benefits to only the SELECTEDVALUE of the previous_product_id, if I COUNTROWS on this it gives me the right number for the previous product, but any time I try to then also apply a filter for the specific benefit into that generated table, or check if it contains the value, it goes wrong (errors, blank tables, or a massive expansion where it shows every single benefit, with most of them blank and only the benefits from the previous product populated).

Thanks in advance for any suggestions.

Solution

  • You could try the following.

    Create a second inactive relationship with previous_product_id. Relationships

    Then create a measure similar to:

    New Option = 
  var curOptions = DISTINCT(Benefit[benefit_name])
  var prevOptions = 
    CALCULATETABLE(
      DISTINCT(Benefit[benefit_name]),
      USERELATIONSHIP(Contract[previous_product_id], Benefit[product_id])
    )
  var chk = COUNTROWS( INTERSECT(curOptions, prevOptions) )

  return
    SWITCH( TRUE(),
      NOT ISINSCOPE(Benefit[benefit_name]), "",
      ISBLANK(COUNTROWS( curOptions )), BLANK(),
      ISBLANK(chk), "Yes",
      "No"
    )

    Or this measure depending on which you are after (see results below):

    New Option v2 = 
  var curOptions = DISTINCT(Benefit[benefit_name])
  var curProducts = DISTINCT(Contract[product_id])
  var prevOptions = 
    CALCULATETABLE(
      DISTINCT(Benefit[benefit_name]),
      NOT Benefit[product_id] IN curProducts,
      USERELATIONSHIP(Contract[previous_product_id], Benefit[product_id])
    )
  var chk = COUNTROWS( INTERSECT(curOptions, prevOptions ) )

  return
    SWITCH( TRUE(),
      NOT ISINSCOPE(Benefit[benefit_name]), "",
      ISBLANK(COUNTROWS( curOptions )), BLANK(),
      ISBLANK(chk), "Yes",
      "No"
    )

    To give you a result of: Result