Maximum Flag/Value over 2 columns

[Mal_Sondh]

Hi,

If i wanted to create a filter for a chart and only wanted to include the Max Versions per Month end, how can i do this - example data as follows

Snap Key	Snap Date	Month End	Version
1	2021-02-02	2021-01-31	1
2	2021-02-06	2021-01-31	2
3	2021-03-02	2021-02-28	1
4	2021-04-02	2021-03-30	1
5	2021-04-04	2021-03-30	2
6	2021-04-04	2021-03-30	3

When i apply the filter i should only see the following:

Snap Key	Snap Date	Month End	Version
2	2021-02-06	2021-01-31	2
3	2021-03-02	2021-02-28	1
6	2021-04-04	2021-03-30	3

Any help would be appreciated.

[Anonymous · Accepted solution]

Hi @Mal_Sondh 

You can create a measure and then put the measure in the filter .

max version =

var max_version=CALCULATE(MAX('Table'[Version]),ALLEXCEPT('Table','Table'[Month End]))

return IF(SELECTEDVALUE('Table'[Version])=max_version,1,0)

Then put the measure in the filter ,set the value is equal to 1 ,

The effect is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[amitchandak]

@Mal_Sondh , Create measure like these for snap key and version and use with Month, take max of date if needed

Version =
VAR __id = MAX ('Table'[Month ] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Month ] = __id )
CALCULATE ( max ('Table'[End Version] ), VALUES ('Table'[Month] ),'Table'[Month] = __id,'Table'[Date] = __date )

Snap =
VAR __id = MAX ('Table'[Month ] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Month ] = __id )
CALCULATE ( max ('Table'[Snap Key] ), VALUES ('Table'[Month] ),'Table'[Month] = __id,'Table'[Date] = __date )

[Mal_Sondh]

@amitchandak 

so the name of the table is Snapshot - are you referencing any other tables above in your query?