Rewrite Measure

[Anonymous]

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

Date	Employee	Worked Job 1	Worked Job 2	Worked Job 3	Worked Job 4	Worked Job 5

Can this be achieved with any visualisations in Power BI?

[v-yanjiang-msft]

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]

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 · Accepted solution]

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.

[amitchandak]

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

[Anonymous]

Hi, 

Please see the attached excel document showing a sample piece of demo data and the desired output.

https://hertel-my.sharepoint.com/:x:/g/personal/elliot_serdet_altrad_com/ER9Iyrk0_CxHrrEmIO7sOFABRtI...

Many thanks