Calculate totals of matching values in different columns

[Aidanok]

I'm newer to PowerBi and this is probably an easy solve for lots of people but I can't figure it out. I want to get the total times a value is present in a table when that value is in present in multiple colunms. 

Top table is example of table structure and bottom table is desired outcome.

Thank you in advance!

Desired outcome

[v-weiyan1-msft · Accepted solution]

Hi @Aidanok ,

@wini_R nice method! Thank you, for your quick response and the solution provided.
And based on the sample and description you provided, you can also consider the following method.
1.Please try code as below to create Calculated table.

Table = 
DISTINCT ( SELECTCOLUMNS ( 'Projectdetails', "Name", 'Projectdetails'[Lead] ) )

2.Then use the following code to create a Calculated column.

Total projects assocated = 
VAR _Name = 'Table'[Name]
RETURN
    CALCULATE (
        COUNTROWS ( 'Projectdetails' ),
        FILTER (
            'Projectdetails',
            'Projectdetails'[Lead] = _Name
                || 'Projectdetails'[Support] = _Name
        )
    )

Result is as below.

Best Regards,
Yulia Yan

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

[Aidanok]

Thank you for your response. This worked perfectly!!!

[wini_R]

Hi @Aidanok,
I would transform the source table in power query to get the expected result. Here is example query (you can paste it in advance editor):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJKzEsFUkqxOtFKRkCGY14eTBQkZAzkOKWChHwTi7LBQiZwITDXFCYH5Zsh+CCzQELmcHNB+kAiFnARMNcSzSGGBkBWcGIu3CWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Lead = _t, Support = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

And that's the outome: