Power BI Filter on MAX of a column

3
votes

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?

2
powerbipowerbi-desktop

2 Answers

2
votes

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 ()
    )
0
votes

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