cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
murillocosta
Helper I
Helper I

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

 

murillocosta_0-1671665404921.png

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @murillocosta 

please try

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

View solution in original post

6 REPLIES 6
Mahesh0016
Solution Sage
Solution Sage

@murillocosta 

Mahesh0016_0-1671772835826.png

If this post helps, please consider accept as solution to help other members find it more quickly.

FreemanZ
Community Champion
Community Champion

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

murillocosta_2-1672795501884.png

 

murillocosta_1-1672795473548.png

 

 

 

 

@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
Community Champion
Community Champion

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. 

tamerj1
Super User
Super User

Hi @murillocosta 

please try

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors