Count occurrences across 2 columns and able to filter

[lja]

Hi all,

I'm after some guidance please.

In power BI I have a data table as below and trying to summarise it

as an example the data looks like this

I'm trying to populate a table which looks at the name accross primary and seconday and counts how may time it occures in the year and totals it.

Could anyone provide some guidance on how to do this please?

[lja]

Thanks you.

could you confirm what the below [primary] relates to please?

[Primary]

[smpa01]

@lja  it relates to the column name is the slicer table.

[lja]

I get this error message?

A table of multiple values was supplied where a single value was expected.

[smpa01]

@lja  please follow the pbix attached

[lja]

Don't worry, the pbix didn't help explain it. I need someone to explain rather than send the file as this sits within a larger report.

Thanks for you time

[smpa01]

@lja  What is the exact end result desired?

[lja]

HI, 

This is the result i'd like:

[smpa01]

@lja  firstly you need to build a slicer table like this

Slicer =
FILTER (
    DISTINCT (
        UNION ( VALUES ( 'Table'[Primary] ), VALUES ( 'Table'[Secondary] ) )
    ),
    [Primary] <> BLANK ()
)

and then you can use a measure like this

Measure =
VAR _primary =
    CALCULATE (
        COUNT ( 'Table'[Year] ),
        TREATAS ( VALUES ( Slicer[Primary] ), 'Table'[Primary] )
    )
VAR _secondary =
    CALCULATE (
        COUNT ( 'Table'[Year] ),
        TREATAS ( VALUES ( Slicer[Primary] ), 'Table'[Secondary] )
    )
RETURN
    _primary + _secondary

which gives you this