Forum Discussion

Michael09's avatar
Michael09
Frequent Visitor
8 years ago
Solved

COUNT including wildcard

Hi

Totally new to Power BI and DAX but familiar with SQL.

I'm trying to use a measure to count the number of unique refs in a field that begin with the number 7

 

The field contains data like:

 

71234

73265

36584

75657

56834

46984

 

So in this example I would want the result to return '3'

 

Many thanks

  • MikeJohnsonZA's avatar
    MikeJohnsonZA
    8 years ago

    Hi

     

    You can use the distinct count function to count distinct values and the calculate function to filter for the numbers starting with 7,

    your measure could look something like this

     

    My Measure:=CALCULATE(DISTINCTCOUNT(Table1[MyValue]),left(Table1[MyValue],1)="7")

     # You would need to change the table and column name

3 Replies

  • MikeJohnsonZA's avatar
    MikeJohnsonZA
    Responsive Resident
    8 years ago

    Hi

     

    You can use the distinct count function to count distinct values and the calculate function to filter for the numbers starting with 7,

    your measure could look something like this

     

    My Measure:=CALCULATE(DISTINCTCOUNT(Table1[MyValue]),left(Table1[MyValue],1)="7")

     # You would need to change the table and column name

    • PhilDanley's avatar
      PhilDanley
      Advocate I
      4 years ago

      Thank you for the post, Mike. Solved my failed wildcard attempt for filtering in CALCULATE.