How to Countif Multiple Columns in PBI?

[pbihelpplz]

Hi everyone,

I am new to PBI, not sure if my question is too basic. I have a data table in this structure 

other field	field 1	field 2	field 3	other field
	name 1	null	null
	name 2	name 1	null
	name 1	name 2	name 3

And I want to count how many times each name appears in 3 columns (field 1/2/3), so the result (visualization) would be another table like

Name	# of count
name 1	3
name 2	2
name 3	1
...	.......

I tryied COUNTA function, and add COUNTA(field1)+COUNTA(field2)+COUNTA(field3) but the result seems wrong. 

Much appreciate your help on this. 

[tamerj1 · Accepted solution]

Hi @pbihelpplz 
I assume no relationship betweem the two tables

=
VAR CurrentName =
    SELECTEDVALUE ( Table2[name] )
RETURN
    COUNTROWS (
        FILTER (
            Table1,
            TableName[field 1] = CurrentName
                || TableName[field 2] = CurrentName
                || TableName[field 3] = CurrentName
        )
    )

[pbihelpplz]

wow, it's working! thank you so much @tamerj1 

[SpartaBI]

@pbihelpplz best you unpivot the columns field 1, field 2, field 3 in Power Query before loading to the model. Then it will be straighforward.

Here is a link to download a sample solution file:
How to Countif Multiple Columns in PBI 2022-08-08.pbix

Showcase Report – Contoso By SpartaBI



      

[pbihelpplz]

Thank you SpartaBI. 

I have other fields as values, and I tried the unpivot method but the value fields become duplicated or even triplicated. I am sure there is a way to fix this but I don't know how:) still, thanks a lot for your input!

[SpartaBI]

@pbihelpplz my pleasure, hope I could help the next time 🙂
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543