Forum Discussion

Key123's avatar
Key123
Regular Visitor
2 years ago
Solved

Most Frequent value working with slicer

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

5 Replies

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    2 years ago

    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.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

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

    • Key123's avatar
      Key123
      Regular Visitor
      2 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        2 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.