PowerBI - Count instances of string in multiple columns

Question

Been searching the forums on here but can't find anything that exactly replicates what I'm trying to do - I have split a string by delimiter and currently have an Excel file that has the following column names and some basic sample data:

Learnt 1 - Learnt 2 - Learnt 3 - Learnt 4

Books - (blank) - (blank) - (blank)

Online - Books - (blank) - (blank)

(blank) - Books - Bootcamp - (blank)

Bootcamp - (blank) - Books - (blank)

The four learnt columns are populated either by a method of learning (of which there's around 8 possibilities which apply to every column, or are blank, and more than one column can be non-blank at the same time in any given row. Very simply, I want to be able to count the total number of times each method appears in all the columns in PowerBI, so the expected results here would be Books 4, Online 1, Bootcamp 2. Any help would be greatly appreciated.

I have tried using the USERelationship function to link this to a separate table with all methods of learning listed out(Table 1), see query below but was having no luck:

CountinlearntocodeColumns =

CALCULATE (
    COUNTROWS ( LearningMethodTable ),
    USERELATIONSHIP ( Table1[Method], LearningMethodTable[Learnt 1] )
)
    + CALCULATE (
        COUNTROWS ( LearningMethodTable ),
        USERELATIONSHIP ( Table1[Method], LearningMethodTable[Learnt 2] )
    )
    + CALCULATE (
        COUNTROWS ( LearningMethodTable ),
        USERELATIONSHIP ( Table1[Method], LearningMethodTable[Learnt 3] )
    )

You need to show your data model if you're using USERELATIONSHIP() and want assistance. — davidebacci
– davidebacci, Commented Sep 18, 2022 at 10:54

Peter · Accepted Answer · 2022-09-18 11:33:13Z

1

You make life unnecessarily difficult when working with pivoted tables in Power BI. Fix that first

In Power Query simply unpivot all columns and filter out blanks:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrPzy5WUNJRgqJYnWgl/7yczLxUkCCKLEgKWQzIKElOzC1QgEmiCCBrjo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Learnt 1 " = _t, #"Learnt 2 " = _t, #"Learnt 3 " = _t, #"Learnt 4" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(
        Source, {}, "Learnt", "Method"),
    #"Filtered Rows" = Table.SelectRows(
        #"Unpivoted Columns", each [Method] <> null and [Method] <> "")
in
    #"Filtered Rows"

The result will look like this:

Now you can easily create your count in DAX

Count = Count('Table'[Method])

and pull it into a new result table:

edited Sep 18, 2022 at 11:33

answered Sep 18, 2022 at 11:18

Peter

12.7k2 gold badges37 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Over a year ago

How to append multiple columns in one column

gitit312 Over a year ago

thank you sir - can confirm this solved my question.

Collectives™ on Stack Overflow

PowerBI - Count instances of string in multiple columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related