CODEWITHSUNDEEP
powerbipowerbi-desktop
tonyyeb
tonyyeb

Reputation: 719

Power BI Filter on MAX of a column

My data has a column with the week number. I want a visual (graph in this case) to show the latest week which is the MAX of that value. The visual filter doesn't appear to let me put in the MAX of something, just the usual greater than, less than etc.

I can use a slicer to get the latest week but I want just one visual to show it. The rest of the report will be all weeks.

Any ideas?

Upvotes: 4

Views: 14566

Answers (3)

Olivia Mogg
Olivia Mogg

Reputation: 11

You can also use the 'Filters' function on the chart where data field is the key metric (e.g. sales), Filter type = 'Top N', Show items = 'Top' / '1', By value = time period variable.

This worked for me :)

Upvotes: 1

Marco Vos
Marco Vos

Reputation: 2968

If you only want the data of the latest week in your visual, you can add a calculated column like this:

OnlyLatestWeek = IF('Table1'[Weeknr] = MAX('Table1'[Weeknr]),Table1[Weeknr],BLANK())

Use this column in your visual in stead of the [Weeknr] column.

EDIT
In DirectQuery mode you can try using a measure in stead of a calculated column. Something like this:

OnlyLatestWK = 
VAR maxweek =
    CALCULATE ( MAX ( 'Table1'[Week] ), ALL ( Table1 ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Table1'[Week] ) = maxweek,
        SUM ( Table1[Amount] ), //use your own expression here
        BLANK ()
    )

Upvotes: 3

Ashish Karnavat
Ashish Karnavat

Reputation: 96

You can create one measure as MAX[TableName.Week] and drag it to your visual.

Upvotes: 0

Related Questions