Most Frequent value working with slicer

[Key123]

I need help with the displaying the most frequent value in Card visualisation while the selected filters in slicers. 

I have tried to create new measure with the most frequest month:

TopMonth =

CALCULATE(SELECTEDVALUE('Tablename'[Date].[Month]),

TOPN(1,ALL('Tablename'),[Date].[Month],DESC))

But after putting new measure in card visualisation the result is the same month, even the filters in slicers are selected. 

Could you please help with the working formula for the most frequent value, which is working with the slicers? 

Thank you

[Anonymous · Accepted solution]

Hi @Key123 ,

Based on your description, I created simple samples and you can check the results below:

Most Frequent = 
var _table=ADDCOLUMNS(ALLSELECTED('Table'),"year",YEAR('Table'[Data]),"month",MONTH('Table'[Data]))
var _b=  SUMMARIZE(_table,[month],"count of id",COUNT('Table'[ID]))
return CALCULATE(MAXX(FILTER(_b,[count of id]=MAXX(_b,[count of id])),[month]))

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Ashish_Mathur]

Hi,

Your requirement is not clear.  Clearly show the result you are expecting.

[Key123]

Hello, 

I have the column with dates: 

Trying to make card type visualisation with the most frequent Month based on the filters in slicers.

How the result should be shown: 

The example of slicer:

While selecting specific year in slicer, the value in the card visualisation (TopMonth) is not changing. 

[Ashish_Mathur]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create the year slicer from the Calendar Table.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  Write these measures

Measure = countrows(Data)

Meausre 1 = MAXX(VALUES(Calendar[Month name]),[Measure])

Hope this helps.

[vicky_]

Here's something using some of my own sample data: 

Most common month = 
var months = VALUES('Table'[Month])
return TOPN(1, months, 'Table'[Month], ASC, COUNT('Table'[Month]))

Hope this helps.