Forum Discussion
Helper IIMaximum Flag/Value over 2 columns
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.
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.
3 Replies
- amitchandak
Super User
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_Sondhso the name of the table is Snapshot - are you referencing any other tables above in your query?
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.
