Max for distinctcount in a group

[Anonymous]

Hi all,

I need to find a number that represents biggest quantity of events in each year and return maximum for whole period of time.

So my source data is below:

When i add following DAX expression 

Measure = 
CALCULATE (
          DISTINCTCOUNT(Table[Id_Event]); 
          GROUPBY(Table1;Table1[Year])
                       )

I get the following table:

What i need know is just a number 8, that represents max events in whole period of time.

Below is link to excel source data but i don't know if it will work as i am inserting link for the first time in this forum.

Does it work?

https://www.dropbox.com/s/nkv5tiotriawhgb/gauge%20chart%20source.xlsx?dl=0

Best regards 

[Ashish_Mathur · Accepted solution]

Hi,

Write these 2 measures

Count = COUNTROWS(Data)

Max count = MAXX(FILTER(VALUES(Data[Year]),[Count]),[Count])

Drag the second measure to a card visual

Hope this helps.

[Anonymous]

Thank you all for support!

Solution provided by @Ashish_Mathur does the job nicely.

Any idea on how to remove all filters but year from final expression ?

Max count = MAXX(FILTER(VALUES(Data[Year]),[Count]),[Count])

I've tried with ALL function but i failed to achive my goal.

[v-frfei-msft]

Hi @Anonymous ,

To create a measure as below.

Max event in the period = CALCULATE(MAX([ID_Event]),ALLEXCEPT(Tabela2,Tabela2[Year]))