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 

 

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

        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.