Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |