Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tim_morris
Regular Visitor

Count text value appearances across whole table

Hi all,

 

Many thanks in advance for the help! I'm trying to count all appearances of text across a whole table, and get a total count of appearances. I'm aware there's no straight SUMIF in Power BI, but I'm trying to find out the total appearances of certain TEXT across a few columns of my table.

 

Example is;

 

RaceDateCountryFirst FinisherSecond FinisherThird FinisherFouth Finisher
Marathon03/02/2019UKScottBobMaryVijay
Half-Marathon04/02/2019GermanyGerryMaryRobKillian
10K04/05/2019IndiaDianeImogenVirajVijay
5K06/08/2019USABobMaryIanSeamus
1k08/04/2019LaosAliGeoffJohnSimone
100k02/05/2019UKScottBobImogenViraj

 

and i'm trying to calculate the appearance of each across ALL columns of the table, for something like;

 

Total Top 4 Finishes:

Mary 3

Bob 3

Scott 2

Vijay 2

Ian 1 

 

Any idea about how best to solve? I have no Column that details ALL names within the table, so is it possible to 'COUNT' if I don't have all the things to 'FILTER' for first? Thanks in advance for any ideas! @

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @tim_morris 

You can best unpivot the finisher columns first (in PQ), then build a very simple measure. You can also use a slicer to choose if you want to see only fist finishers, 1st and 2nd , all finishers, etc.

See it all at work in the attached file.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

4 REPLIES 4
tim_morris
Regular Visitor

Thanks both - managed the (very simple) unpivot as described by @AlB  - but thanks so much for the help both!

MiraAciu
Regular Visitor

Hi Tim,

 

From my undestanding, you have allready imported the table, so you try to find a way to use DAX in order to accomplish your goal. 

 

If you don't need the original format of the table in your model, AIB solution will work.

 

After import, I'll use UNION to create a new calculated table. I'll use this table in a Table visual with 2 columns (first for participant name, second for Count(participant name).

The DAX I use for calculated table is:

 

Results = UNION(SELECTCOLUMNS('Competitions', "Participant", [First Finisher]),SELECTCOLUMNS(Competitions, "Participant",[Second Finisher]), SELECTCOLUMNS('Competitions',"Participant",[Third Finisher]),SELECTCOLUMNS('Competitions',"Participant",[Fouth Finisher]))
 
AlB
Super User
Super User

Hi @tim_morris 

You can best unpivot the finisher columns first (in PQ), then build a very simple measure. You can also use a slicer to choose if you want to see only fist finishers, 1st and 2nd , all finishers, etc.

See it all at work in the attached file.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Hi AIB,

 

Unpivoting in M was my first choise also, but you were one step ahead :). Congrats! 

My solution is a little bit different, as I choose to use grouping instead measure for counting. What do you think? 

let
Source = Csv.Document(File.Contents("C:\xxxxxxxxxxx\exp BI forum.txt"),[Delimiter=" ", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Race", type text}, {"Date", type date}, {"Country", type text}, {"First Finisher", type text}, {"Second Finisher", type text}, {"Third Finisher", type text}, {"Fouth Finisher", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Race", "Date", "Country"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Value"}, {{"top finishers", each Table.RowCount(_), Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"top finishers", Order.Descending}})
in
#"Sorted Rows"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.