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 emp...
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.

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

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.