Forum Discussion

jaw19883's avatar
jaw19883
Frequent Visitor
5 years ago
Solved

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.  ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    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.

     

AlexisOlson's avatar
AlexisOlson
Super User
5 years ago

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.

 

  • jaw19883's avatar
    jaw19883
    Frequent Visitor
    5 years ago

    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 Triangles

    OR
    G.CO.11 Parallelograms, SRT.5 Similar Triangles, G.CO.5 Transformations in the Coordinate Plane

     

    depending 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's avatar
      AlexisOlson
      Super User
      5 years ago

      In that case, you can use CONTAINSSTRING ( Table1[Section], "G.CO.5" ) instead of the LEFT line.

  • jaw19883's avatar
    jaw19883
    Frequent Visitor
    5 years ago

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