Forum Discussion
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;
| Race | Date | Country | First Finisher | Second Finisher | Third Finisher | Fouth Finisher |
| Marathon | 03/02/2019 | UK | Scott | Bob | Mary | Vijay |
| Half-Marathon | 04/02/2019 | Germany | Gerry | Mary | Rob | Killian |
| 10K | 04/05/2019 | India | Diane | Imogen | Viraj | Vijay |
| 5K | 06/08/2019 | USA | Bob | Mary | Ian | Seamus |
| 1k | 08/04/2019 | Laos | Ali | Geoff | John | Simone |
| 100k | 02/05/2019 | UK | Scott | Bob | Imogen | Viraj |
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! @
Hi Anonymous
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
4 Replies
- AlB
Community Champion
Hi Anonymous
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
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"
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]))Thanks both - managed the (very simple) unpivot as described by AlB - but thanks so much for the help both!
