Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
MartynRamsden
Solution Sage
Solution Sage

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.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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
Not applicable

Hi @Anonymous ,

 

The below measure is not working as expected for me.

 

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

 

44.PNG

 

Thanks

MartynRamsden
Solution Sage
Solution Sage

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
Not applicable

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

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
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.