cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

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

The Tranasction table

OrgOfficeGroup
AN01HH01F01
AN01HH02G03
BO02KL03N03
GR01NN01J01
FD02KL01H01

The price table

Level_1Level_2Level_3
AN01  
BO02  
GR01NN01 
FD02KL01H01

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

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

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

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

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

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Regular Visitor

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

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Come join us today! Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors