topic Re: Filter card when selected and show default value when not selected in DAX Commands and Tips

Filter card when selected and show default value when not selected

winniexlee — Mon, 09 Jan 2023 02:27:36 GMT

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!

Re: Filter card when selected and show default value when not selected

FreemanZ — Mon, 09 Jan 2023 03:05:49 GMT

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])

)

Re: Filter card when selected and show default value when not selected

FreemanZ — Mon, 09 Jan 2023 03:09:49 GMT

hi winniexlee

tried with such dataset:

it worked liked this:

Re: Filter card when selected and show default value when not selected

winniexlee — Mon, 09 Jan 2023 03:23:55 GMT

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?

Re: Filter card when selected and show default value when not selected

FreemanZ — Mon, 09 Jan 2023 03:44:29 GMT

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.

Re: Filter card when selected and show default value when not selected

winniexlee — Mon, 09 Jan 2023 06:44:53 GMT

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.

Re: Filter card when selected and show default value when not selected

FreemanZ — Mon, 09 Jan 2023 07:06:29 GMT

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])

)