cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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.

 

Overlap.PNG

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Overlap scenario

Hi @Prakash_Mathi 

 

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.





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

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Super User I
Super User I

Re: Overlap scenario

Hi @Prakash_Mathi 

 

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.





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

Proud to be a Super User!




View solution in original post

PR20048119 New Contributor
New Contributor

Re: Overlap scenario

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.

Re: Overlap scenario

Hi @PR20048119 ,

 

The below measure is not working as expected for me.

 

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

 

44.PNG

 

Thanks

Re: Overlap scenario

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

Super User I
Super User I

Re: Overlap scenario

Hi @Prakash_Mathi 

 

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.

 





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

Proud to be a Super User!




PR20048119 New Contributor
New Contributor

Re: Overlap scenario

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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors