Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
Could someone help me achieve the below.
I have a table which I need to find the max period_id per per facility_id like the example below 425 to facility 1 and 424 to facility 4.
Once I figure this value out I need to get another column value (exchange_rate) and repeat it to the others rows for that facility_id as shown below.
I can't use power Query as I need to dynamically change this table when the main date slicer is changed
Tks
Solved! Go to Solution.
please try
NewColumn =
MAXX (
TOPN (
1,
CALCULATETABLE ( Table01, ALLEXCEPT ( Table01, Table01[FACILITY_ID] ) ),
Table01[PERIOD_ID]
),
Table01[EXCHANGE_RATE]
)
If this post helps, please consider accept as solution to help other members find it more quickly.
apparently tamerj1's solution worked perfectly and more elegant, but this might be easier to digest:
result2 =
VAR _facility = [FACILITY_ID]
VAR _period =
MAXX(
FILTER(
Table01,
Table01[FACILITY_ID] = _facility
),
Table01[PERIOD_ID]
)
RETURN
MINX(
FILTER(
Table01,
Table01[FACILITY_ID]=_facility&&Table01[PERIOD_ID] = _period
),
Table01[EXCHANGE_RATE]
)
Thanks guys that's really helpfull.
The only thing I forgot to mention is that I also to need to filter the MAX date selected from my calendar table on the slice.
Like in the example below I selected 31/10/2021 which is period 400 for subsidiary 1, but the measure is bringing 426 which is the highest period on the overall table.
Any idea the best whey to apply this calendar date filter?
Thanks
To avoid double scan of the table you can use
result2 =
VAR _facility = [FACILITY_ID]
VAR T =
FILTER ( Table01, Table01[FACILITY_ID] = _facility )
VAR _period =
MAXX ( T, Table01[PERIOD_ID] )
RETURN
MINX ( FILTER ( T, Table01[PERIOD_ID] = _period ), Table01[EXCHANGE_RATE] )
Hi @tamerj1
Thank you very much. That is a good use of table variable. I actually have such double scans very often. It is very insightful. Learned a lot from you.
please try
NewColumn =
MAXX (
TOPN (
1,
CALCULATETABLE ( Table01, ALLEXCEPT ( Table01, Table01[FACILITY_ID] ) ),
Table01[PERIOD_ID]
),
Table01[EXCHANGE_RATE]
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
27 | |
23 | |
22 | |
22 |