Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count selected options in slicer

I have a slicer that sets restricts data for month(s) in my report:

 

I want to create a measure that counts the amount of options selected.

If January and February are chosen, that's 2. 

 

I tried the following but it gave an error about comparing True/False with Text:

 

Measure MonthSlicerCount:

VAR January = IF(ISFILTERED('Date'[MonthYearName]) = "January 2020", 1,0)
VAR February = IF(ISFILTERED('Date'[MonthYearName]) = "February 2020", 1,0)
VAR March = IF(ISFILTERED('Date'[MonthYearName]) = "March 2020", 1,0)
VAR April = IF(ISFILTERED('Date'[MonthYearName]) = "April 2020", 1,0)
VAR May = IF(ISFILTERED('Date'[MonthYearName]) = "May 2020", 1,0)
VAR June = IF(ISFILTERED('Date'[MonthYearName]) = "June 2020", 1,0)
VAR July = IF(ISFILTERED('Date'[MonthYearName]) = "July 2020", 1,0)
VAR August = IF(ISFILTERED('Date'[MonthYearName]) = "August 2020", 1,0)
VAR September = IF(ISFILTERED('Date'[MonthYearName]) = "September 2020", 1,0)
VAR October = IF(ISFILTERED('Date'[MonthYearName]) = "October 2020", 1,0)
VAR November = IF(ISFILTERED('Date'[MonthYearName]) = "November 2020", 1,0)
VAR December = IF(ISFILTERED('Date'[MonthYearName]) = "December 2020", 1,0)*/

VAR MonthSum = January+February+March+April+May+June+july+August+September+October+November+December
RETURN MonthSum
 
If this is do-able, can it also be made so that the text doesn't have to include '2020'? If contains "January", then that is True. 
 
 
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Measure = COUNTROWS(VALUES('Date'[MonthYearName]))

     

    Please note, only using VALUES here because it makes sense to use VALUES. Don't just go throwing VALUE or VALUES into every DAX calculation randomly. This is not for you Anonymous but I've been seeing a lot of needless use of VALUE and VALUES lately. So, when I use it, I want to make sure that people understand that there is a purpose behind it.

  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try to create a column first:

    Month = 'Table'[Date].[Month]

    The try this measure:

    Measure = DISTINCTCOUNT('Table'[Month])

    When you select differet month from different year, it shows:

    When you select same month from different year, it shows the count of select value regardless of year:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Measure = COUNTROWS(VALUES('Date'[MonthYearName]))

     

    Please note, only using VALUES here because it makes sense to use VALUES. Don't just go throwing VALUE or VALUES into every DAX calculation randomly. This is not for you Anonymous but I've been seeing a lot of needless use of VALUE and VALUES lately. So, when I use it, I want to make sure that people understand that there is a purpose behind it.

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please try to create a column first:

    Month = 'Table'[Date].[Month]

    The try this measure:

    Measure = DISTINCTCOUNT('Table'[Month])

    When you select differet month from different year, it shows:

    When you select same month from different year, it shows the count of select value regardless of year:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi