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

FreemanZ's avatar
FreemanZ
Super User
3 years ago

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

 

  • 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. 

  • murillocosta's avatar
    murillocosta
    Helper I
    3 years ago

    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