CODEWITHSUNDEEP
countpowerbi
gitit312
gitit312

Reputation: 45

PowerBI - Count instances of string in multiple columns

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] )
    )

Upvotes: 1

Views: 1819

Answers (1)

Peter
Peter

Reputation: 12375

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

  1. 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:

enter image description here

  1. Now you can easily create your count in DAX
Count = Count('Table'[Method])

and pull it into a new result table:

enter image description here

Upvotes: 1

Related Questions