How to appy a incomplete multi-field key in a column field

[BasB]

In my data model I have a combination of fields that match with between two tables.

The Tranasction table

Org	Office	Group
AN01	HH01	F01
AN01	HH02	G03
BO02	KL03	N03
GR01	NN01	J01
FD02	KL01	H01

The price table

Level_1	Level_2	Level_3
AN01
BO02
GR01	NN01
FD02	KL01	H01

However in the price table some lines don’t have all fields defined. 

In fact the first line of the price table do match will all transactions where Org = Level_1 (AN01 = AN01)

While in case of the third line of the price table the link between the two tables is based on 2 columns:

Org = Level_1 (GR01 = GR01) and Office = Level_2 (NN01 = NN01)

While in case of the last line of the price table the link between the two tables is based on 3 columns:

Org = Level_1 (GR01 = GR01) and Office = Level_2 (NN01 = NN01) and Group = Level_3 (H01 = H01)

In fact the lowest level of detail is leading, if it is not possible to fetch a lower level the higher level should be applied.

How can this be build in this column definition:

Country_Price = 
var _dt = maxx(
  filter(
    Price,
    Price[ProductID] = Transaction[ProductID]
    && Price[Level_1] = Transaction[Org]
    && Price[Level_2] = Transaction[Office]
    && Price[Level_3] = Transaction[Group]
    && Price[Date_valid_from] < Transaction[Date]
    && Price[Type of Price] = "Country"
  ),
  Price[Date_valid_from]
)
return maxx(
  filter(
    Price,
    Price[ProductID] = Transaction[ProductID] 
    && Price[Level_1] = Transaction[Org]
    && Price[Level_2] = Transaction[Office]
    && Price[Level_3] = Transaction[Group]
    && Price[Date_valid_from] = _dt
    && Price[Type of Price] = "Country"
  ),
  Price [Price]
)

[BasB · Accepted solution]

I have found a solution:

Country_Price = 
var _dt = maxx(
  filter(
    Price,
    Price[ProductID] = Transaction[ProductID]
    && if(
      Price[Level_3] = BLANK(),
      if(Price[Level_2] = BLANK(),
        Price[Level_1] = Transaction[Org],
        Price[Level_1] = Transaction[Org]
        && Price[Level_2] = Transaction[Office]
      ),
      Price[Level_1] = Transaction[Org]
      && Price[Level_2] = Transaction[Office]
      && Price[Level_3] = Transaction[Group]
    )
     && Price[Date_valid_from] < Transaction[Date]
    && Price[Type of Price] = "Country"
  ),
  Price[Date_valid_from]
)
return maxx(
  filter(
    Price,
    Price[ProductID] = Transaction[ProductID] 
    && if(
      Price[Level_3] = BLANK(),
      if(Price[Level_2] = BLANK(),
        Price[Level_1] = Transaction[Org],
        Price[Level_1] = Transaction[Org]
        && Price[Level_2] = Transaction[Office]
      ),
      Price[Level_1] = Transaction[Org]
      && Price[Level_2] = Transaction[Office]
      && Price[Level_3] = Transaction[Group]
    )
    && Price[Date_valid_from] = _dt
    && Price[Type of Price] = "Country"
  ),
  Price [Price]
)

[v-eachen-msft]

Hi @BasB ,

Could you please share your sample data and expected result to me if you don't have any Confidential Information? Please upload your files to OneDrive for Business and share the link here.