Overlap scenario

[Anonymous]

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

[Anonymous]

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]

Hi @Anonymous ,

The below measure is not working as expected for me.

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

Thanks

[MartynRamsden · Accepted solution]

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]

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]

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]

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

replace measue with project count..it will work for any size of dataset.

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

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