Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Overlap scenario

Hi All,

 

I have Employee name and project name against them. Then i created a measuer which tells the total count of Projects in which they are working.

 

Now i would like to get those employee names alone who are working in more than 1 project.

Here in the above example Paul and Alan, both of these guys are working in more than 1 projects. so i would like to have a columnn which gives the names of these 2 employees alone. 

Also another column which holds "The total count of employess working in more than 1 project" i.e., 2.

 

Thanks in Advance

Need Help
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    Hi Anonymous 

     

    Add a caluclated column to identify Names which have more than 1 project:

    Project Count = 
VAR RowName = Table1[Name]
VAR ProjectCount = 
CALCULATE ( 
    DISTINCTCOUNT ( Table1[Project] ),
    FILTER ( 
        ALL ( Table1 ),
        Table1[Name] = RowName
    )
)
VAR Result = 
IF ( 
    ProjectCount > 1,
    "Y",
    "N"
)
RETURN Result

     

    You can then use this column as a visual level filter on your table visualisation to display only the Names with more than 1 project.

     

    The following measure will give you the number of Names with more than 1 project:

    More Than 1 Project = 
CALCULATE (
    DISTINCTCOUNT ( Table1[Name] ),
    Table1[Project Count] = "Y"
)

     

    Best regards,
    Martyn


    If I answered your question, please help others by accepting it as a solution.

6 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    Hi Anonymous 

     

    Add a caluclated column to identify Names which have more than 1 project:

    Project Count = 
VAR RowName = Table1[Name]
VAR ProjectCount = 
CALCULATE ( 
    DISTINCTCOUNT ( Table1[Project] ),
    FILTER ( 
        ALL ( Table1 ),
        Table1[Name] = RowName
    )
)
VAR Result = 
IF ( 
    ProjectCount > 1,
    "Y",
    "N"
)
RETURN Result

     

    You can then use this column as a visual level filter on your table visualisation to display only the Names with more than 1 project.

     

    The following measure will give you the number of Names with more than 1 project:

    More Than 1 Project = 
CALCULATE (
    DISTINCTCOUNT ( Table1[Name] ),
    Table1[Project Count] = "Y"
)

     

    Best regards,
    Martyn


    If I answered your question, please help others by accepting it as a solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi MartynRamsden ,

       

      For larger dataset it is taking more time than expected and performance of the dashboard is getting hit.

       

      Is there any other way to do it?

       

      Thanks

      • MartynRamsden's avatar
        MartynRamsden
        Solution Sage
        6 years ago

        Hi Anonymous 

         

        I'm surprised that the measure performance is slow as the query plan should be optimal.

        The slow part would be calculating the column on data load / refresh, which wouldn't impact the report user.

         

        Try this measure instead:

        >1 Project =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table1[Name] ),
            "@Projects", CALCULATE ( COUNTROWS ( VALUES ( Table1[Project] ) ) )
        ),
        [@Projects] > 1
    )
)

         

        One benefit of this approach is that the measure can also be used as the visual level filter on your table visualisation which shows the Names with more than 1 project. This means that the calculated column in my previous answer is not required.

         

        Hope it helps.

         

        Best regards,
        Martyn


        If I answered your question, please help others by accepting it as a solution.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi create simple measure

     

    measure= count(table[Projects])

     

    and drag emp name and measure in table visual and add measure into visual level filter and set it to "is greater than 1"

     

    to get total employees having greater than 1

     

    measure2=Sumx(table,if([measure]>1,1,0))

     

     

    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      The below measure is not working as expected for me.

       

      measure2=Sumx(table,if([measure]>1,1,0))

       

       

      Thanks