Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rewrite Measure

I have the below measure.

 

Employee 5+ Jobs =
VAR newtable =
ADDCOLUMNS (
SUMMARIZE ( Employees, Employees[Employee Name] ),
"@jobcount", CALCULATE ( COUNTROWS ( VALUES ( 'Employees'[Job] ) ))
)
VAR filternewtable =
FILTER ( newtable, [@jobcount] >= 5 )
RETURN
IF( HASONEVALUE(Employees[Date] ),
COUNTROWS ( filternewtable ))
 
This measure looks at a table of jobs then summarizes a count where certain employees work 5+ jobs in one day.
 
I want to display this information in a table. This table would need to look like the following
 
DateEmployeeWorked Job 1 Worked Job 2 Worked Job 3 Worked Job 4Worked Job 5
       
       

 

Can this be achieved with any visualisations in Power BI?

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    Did you mean there's blank value in Job column like this?

    If this is the case, you can modify the formula of Rank like this:

    Rank =
IF (
    'Table'[Job] = BLANK (),
    BLANK (),
    RANKX (
        FILTER (
            'Table',
            'Table'[Date] = EARLIER ( 'Table'[Date] )
                && 'Table'[Employee Name] = EARLIER ( 'Table'[Employee Name] )
                && 'Table'[Job] <> BLANK ()
        ),
        'Table'[Job],
        ,
        ASC,
        DENSE
    )
)

    Then it can get the correct rank.

    Best Regards,
    Community Support Team _ kalyj

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous ,
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    According to your description, it's hard to transform data to get the expected result in PowerQuery. Here's my solution:

    1.Create a rank column.

    Rank =
RANKX (
    FILTER (
        'Table',
        'Table'[Date] = EARLIER ( 'Table'[Date] )
            && 'Table'[Employee Name] = EARLIER ( 'Table'[Employee Name] )
    ),
    'Table'[Job],
    ,
    ASC,
    DENSE
)

    Get the rank belong to the same date and same employee.

    2.Create measures.

    Job1 = 
IF (
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[Employee Name] = MAX ( 'Table'[Employee Name] )
        ),
        'Table'[Rank]
    ) >= 5,
    CALCULATE ( MAX ( 'Table'[Job] ), 'Table'[Rank] = 1 )
)

    Job2 = 
IF (
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[Employee Name] = MAX ( 'Table'[Employee Name] )
        ),
        'Table'[Rank]
    ) >= 5,
    CALCULATE ( MAX ( 'Table'[Job] ), 'Table'[Rank] = 2 )
)

    Job3 = 
IF (
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[Employee Name] = MAX ( 'Table'[Employee Name] )
        ),
        'Table'[Rank]
    ) >= 5,
    CALCULATE ( MAX ( 'Table'[Job] ), 'Table'[Rank] = 3 )
)

    Job4 = 
IF (
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[Employee Name] = MAX ( 'Table'[Employee Name] )
        ),
        'Table'[Rank]
    ) >= 5,
    CALCULATE ( MAX ( 'Table'[Job] ), 'Table'[Rank] = 4 )
)

    Job5 = 
IF (
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[Employee Name] = MAX ( 'Table'[Employee Name] )
        ),
        'Table'[Rank]
    ) >= 5,
    CALCULATE ( MAX ( 'Table'[Job] ), 'Table'[Rank] = 5 )
)

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you very much for taking the time to look at my post.

       

      This works great however where my data sometimes shows column 'Job' as null (blank), the formular counts this null row as a job. Is there anyway to exclude blanks from this?

       

      Many thanks

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        4 years ago

        Hi Anonymous ,

        Did you mean there's blank value in Job column like this?

        If this is the case, you can modify the formula of Rank like this:

        Rank =
IF (
    'Table'[Job] = BLANK (),
    BLANK (),
    RANKX (
        FILTER (
            'Table',
            'Table'[Date] = EARLIER ( 'Table'[Date] )
                && 'Table'[Employee Name] = EARLIER ( 'Table'[Employee Name] )
                && 'Table'[Job] <> BLANK ()
        ),
        'Table'[Job],
        ,
        ASC,
        DENSE
    )
)

        Then it can get the correct rank.

        Best Regards,
        Community Support Team _ kalyj

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.