Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
)
Solved! Go to 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]
)
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]
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |