Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Count filtering

Hi all, I have this similar table:   Name                              Project Jose Carlos                          A David Fernandez                 B Carlos Andres                      C Jose...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous it actually works when i use a Slicer for Name, but i need it to work with a Project Slicer. Example: in a slicer for project a choose A then the card will show the total projects of the person for the Project A in this case 3 projects.

 

Thanks

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

You can use the below measure:

 

COUNT of Projects =
VAR _cc =
    COUNT ( ProjectDetials[Name] ) 
VAR _sel =
    SELECTEDVALUE ( ProjectDetials[Name] ) 
VAR _sel1 =
    SELECTEDVALUE ( ProjectDetials[Project] ) 
VAR _name =
    VALUES ( ProjectDetials[Name] ) 
VAR _ccx =
    CALCULATE (
        COUNT ( ProjectDetials[Project] ),
        FILTER (
            ALL ( ProjectDetials ),
            ProjectDetials[Name]
             =
                _name
        )
    )
return
    SWITCH (
        TRUE (),
        NOT ( ISBLANK ( _sel1 ) ),
        CONCATENATE (
            CONCATENATE ( CONCATENATE ( _name, " has " ), _ccx ),
            " projects "
        ),
        NOT ( ISBLANK ( _sel ) ),
        CONCATENATE (
            CONCATENATE ( CONCATENATE ( _sel, " has " ), _cc ),
            " projects "
        ),
        ISBLANK ( _sel ),
        CONCATENATE ( "Total Project ", _cc ),
        ISBLANK ( _sel1 ),
        CONCATENATE ( "Total Project ", _cc ),
        BLANK ()
    )

 

 

Regards,

Harsh Nathani

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous Hi, I tried it but still the filter for name has to be marked. Also I need it to just show the value 3 and not Jose Carlos has 3 projects. 

     

     
     
    COUNT of Projects =
    VAR _cc =
    COUNT ( Lider[Líder] )
    VAR _sel =
    SELECTEDVALUE ( Lider[Líder])
    VAR _sel1 =
    SELECTEDVALUE ( Lider[Proyecto] )
    VAR _name =
    VALUES ( Lider[Líder] )
    VAR _ccx =
    CALCULATE (
    COUNT ( Lider[Proyecto] );
    FILTER (
    ALL ( Lider );
    Lider[Líder]
    =
    _name
    )
    )
    return
    SWITCH (
    TRUE ();
    NOT ( ISBLANK ( _sel1 ) );
    CONCATENATE (
    CONCATENATE ( CONCATENATE ( _name; " has " ); _ccx );
    " projects "
    );
    NOT ( ISBLANK ( _sel ) );
    CONCATENATE (
    CONCATENATE ( CONCATENATE ( _sel; " has " ); _cc );
    " projects "
    );
    ISBLANK ( _sel );
    CONCATENATE ( "Total Project "; _cc );
    ISBLANK ( _sel1 );
    CONCATENATE ( "Total Project "; _cc );
    BLANK ()
    )

    Thanks a lot. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous ,

       

      Not sure how the filter of name can be marked when you are selecting the projects. These are 2 independant objects.

       

      Anyways, here is the code for showing the no. of projects i.e value 3.

       

      Count of Projects =
      var _cc = COUNT(ProjectDetials[Name])
      Var _sel = SELECTEDVALUE(ProjectDetials[Name])
      var _sel1 = SELECTEDVALUE(ProjectDetials[Project])
      var _name = VALUES(ProjectDetials[Name])
      var _ccx = CALCULATE(COUNT(ProjectDetials[Project]),FILTER(ALL(ProjectDetials),ProjectDetials[Name] = _name))
      return
      SWITCH(
      true(),
      NOT(ISBLANK(_sel1)),_ccx,
      NOT(ISBLANK(_sel)),_cc,
      ISBLANK(_sel),_cc,
      ISBLANK(_sel1),_cc,
      BLANK()
      )
       
      Regards,
      Harsh Nathani
      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!