Filter card when selected and show default value when not selected

[winniexlee]

Hi Experts,

I have this data here whereas my table has these 3 columns:

1. Month

2. Year

3. Employee Name

I want my Card to show the distinctcount of employee name on the latest Month & Year by default, and show the selected Month and Year if there is any selection. 

Kindly advise! Thank you!

[FreemanZ]

hi   @winniexlee 

try like:

Measure = 

VAR _MaxYear = CALCULATE(MAX(TableName[Year]), ALL(TableName[Year]))

VAR _MaxMonth =

MAXX(

    FILTER(TableName, TableName[Year] = _MaxYear), 

    TableName[Month]

)

VAR _CountMax = 

CALCULATE(

    DISTINCTCOUNT(TableName[Employee Name]),

    TableName[Year] = _MaxYear,

    TableName[Month] =_MaxMonth

 )

RETURN

IF(

    NOT ISFILTERED(TableName[Year]) || ISFILTERED(TableName[Month]),

    _CountMax,

    DISTINCTCOUNT(TableName[Employee Name])

)

[FreemanZ]

hi @winniexlee 

tried with such dataset:

it worked liked this:

[winniexlee]

Hihi, the result is not showing correctly.

I forgotten to mention that i have a column name "Employment Type" in another table, whereas i only want to look at those that are labour supply or permanent employees. Is it possible to use Month Name instead of Month Number also?

[FreemanZ]

hi @winniexlee 

1. you can place a slicer or filter for labour supply or permanent types.

2. Month Name instead of Month Number? seems fine. Just give it a try. 

p.s. please consider @someone, if you seek further suggestions. 

[winniexlee]

@FreemanZ apologies, I already have a Slicer for Month, Year and Employment Type.

HeadcountByDate =

VAR _MaxYear = CALCULATE(MAX(TableName[Year]), ALL(TableName[Year]))

VAR _MaxMonth =

MAXX(

    FILTER(TableName [Year] = _MaxYear),

    TableName [Month Name]

)

VAR _CountMax =

CALCULATE(

    DISTINCTCOUNT(TableName [NAME]),

    TableName [Year] = _MaxYear,

    TableName [Month Name] =_MaxMonth,

    OR(TableName [Emp Type] = "Labour Supply",TableName [Emp Type] = "Perm" ))

RETURN

IF(

    NOT ISFILTERED(TableName [Year]) || NOT ISFILTERED(TableName [Month Name]),

    _CountMax,

    CALCULATE(

    DISTINCTCOUNT(TableName [NAME]),TableName [Emp Type] = "Labour Supply" || TableName [Emp Type] = "Perm" ))

The above is working for the default value. But whenever i selected either a Year/Month/Employment Type, the number is a little bit off.

[FreemanZ]

hi @winniexlee  

try like:

HeadcountByDate =

VAR _MaxYear = CALCULATE(MAX(TableName[Year]), ALL(TableName[Year]))

VAR _MaxMonth =

MAXX(

    FILTER(TableName [Year] = _MaxYear),

    TableName [Month Name]

)

VAR _CountMax =

CALCULATE(

    DISTINCTCOUNT(TableName [NAME]),

    TableName [Year] = _MaxYear,

    TableName [Month Name] =_MaxMonth,

     ALL(TableName [Emp Type])

RETURN

IF(

    NOT ( ISFILTERED(TableName[Year]) || ISFILTERED(TableName[Month Name]) || ISFILTERED(TableName[Emp Type] )

    _CountMax,

    DISTINCTCOUNT(TableName [NAME])

)