Forum Discussion

winniexlee's avatar
winniexlee
New Member
3 years ago

Filter card when selected and show default value when not selected

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 & Ye...
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi winniexlee 

tried with such dataset:

 

it worked liked this:

 

winniexlee's avatar
winniexlee
New Member
3 years ago

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

    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's avatar
      winniexlee
      New Member
      3 years ago

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

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