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 = C...
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.

     

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.