Forum Discussion

murillocosta's avatar
murillocosta
Helper I
3 years ago
Solved

calculated column

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...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi murillocosta 

    please try

    NewColumn =
    MAXX (
    TOPN (
    1,
    CALCULATETABLE ( Table01, ALLEXCEPT ( Table01, Table01[FACILITY_ID] ) ),
    Table01[PERIOD_ID]
    ),
    Table01[EXCHANGE_RATE]
    )

tamerj1's avatar
tamerj1
Community Champion
3 years ago

FreemanZ 

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

FreemanZ's avatar
FreemanZ
Super User
3 years ago

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.