Forum Discussion
Like a COUNTIF in Excel
Ok - Here is my question. Very new to Power Bi, but well-versed in Excel. I have a column of data, that unfortunatley is not the best to play with, but none the less it is exported this way to me.
Think of an online course that has an exported (.CSV) gradebook as the data source. There is a column named "Section". Students can be enrolled in 1 or more sections. So the data for several records for the Section column might look like,
"G.CO.5 Transformations in the Coordinate Plane"
"G.CO.5 Transformations in the Coordinate Plane and G.CO.9 Angles and Parallel Lines"
"G.CO.11 Parallelogram Theorems, G.CO.5 Transformations in the Coordinate Plane, G.SRT.5 Congruent Triangles, and G.SRT.8 Right Triangle Trigonometry"
What I need to do is count the number of instances of G.CO.5 Transformations in the Corrdinate Plane (and all the other sections). This will tell me how many students are enrolled in the particular section. Doing a count where it is the only section listed, such as the first example of the data in the record, is no problem, but I need to also be able to count if the student is in multiple sections.
In Excel I would just do a COUNTIF looking for "*G.CO.5*" (all sections have a different pre-fix combination, so G.CO.5 is unique) and it would return my number.
Now that we are switching our reporting to Power Bi, I want to write a measure to count these enrollments. I have all of my other measures figured out, there just seems to be no direct path for a COUNTIF.
The general pattern is to use filters. (See also the recommendations links in the sidebar.)
CALCULATE ( COUNT ( Table1[Section] ), FILTER ( VALUES ( Table1[Section] ), CONTAINSSTRING ( Table1[Section], "G.CO.5" ) ) )Edit: Changed LEFT condition to CONTAINSSTRING.
5 Replies
- AlexisOlson
Super User
The general pattern is to use filters. (See also the recommendations links in the sidebar.)
CALCULATE ( COUNT ( Table1[Section] ), FILTER ( VALUES ( Table1[Section] ), CONTAINSSTRING ( Table1[Section], "G.CO.5" ) ) )Edit: Changed LEFT condition to CONTAINSSTRING.
That is my problem is the depth of FILTER. If there is only 1 section listed, (G.CO.5 Tranasformations....) it is a simple, ="G.CO.5 Transformations in the Coordinate Plane" and it works.
My problem is that the section could be 1 of multiple in the data, and I do not know which order it will be in. for instance, the data in the Section column for a record could be:
G.CO.5 Transformations in the Coordinate Plane, G.CO.11 Parallelograms, SRT.5 Similar Triangles
OR
G.CO.11 Parallelograms, G.CO.5 Transformations in the Coordinate Plane, SRT.5 Similar TrianglesOR
G.CO.11 Parallelograms, SRT.5 Similar Triangles, G.CO.5 Transformations in the Coordinate Planedepending on when they enrolled in the section. Therefore, there is no pattern as to when the section will appear so I need to use a (*) to evaluate *G.CO.5 Transformations in the Coordinate Plane* wherever it may appear in the data string.
- AlexisOlson
In that case, you can use CONTAINSSTRING ( Table1[Section], "G.CO.5" ) instead of the LEFT line.
AlexisOlson That worked! Thank you! You also helped me start to see how the DAX logic works a bit differently. I look forward to understanding that more. Thanks again....
