DAX/POWERPIVOT Count Number of Values That Contain Certain Text

[melq64]

Hi,

I've created this dax measure to count the number of values in a column that contain the text "PO BOX"

TotalPOBox:=COUNTROWS(FILTER('DistProfile v1-3',FIND("PO BOX",'DistProfile v1-3'[Dest_Address],,0)>0))

The formula works fine but the value returned (373) is less than the actual number of "PO BOX" values which should be (394).

Here's a sample of the column values in question:

PO BOX 300404
PO BOX 300712
PO BOX 106045
1 LINCOLN AVE
56 MARYLAND RD
33 LANCASTER RD
PO BOX 200453

I'm obviously missing something and would appreciate any assistance.


Thanks

 

[gazpage]

Too hard to say based on this. Create a table viz, put your lookup column in there and this measure in the values. Then sort by your values column and look for the rows where it returns zero, but you expect a match. Then investigate those.

 

[brawnystaff]

Chose the table name "Table1" and used SEARCH instead of FIND (SEARCH is not case sensitive):

=CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( Table1, SEARCH ( "PO BOX", Table1[Address], 1, 0 ) > 0 )

 

[melq64]

Perfect thank you brawnystaff. That worked

If its ok to ask for assistance regarding same measure, is it possible to search for the FIRST NUMBER only of zipcodes? e.g. search "0","1","2","3" Example below:

0610
0932
0743
1432
1338
2025
2088
3097
4122

 

[brawnystaff]

Assuming zip codes were in their own cell, for one value at at time, it would be as follows:

=CALCULATE(COUNTROWS ( Table1 ),FILTER( Table1,If( LEFT(Table1[Zip],1)="1",1, 0 ) > 0 ))

However, it looking to count based on a range of first numbers, it would be as follows:

=CALCULATE(COUNTROWS ( Table1 ),FILTER( Table1,If( LEFT(Table1[Zip],1)="0"||LEFT(Table1[Zip],1)="1"||LEFT(Table1[Zip],1)="2"||LEFT(Table1[Zip],1)="3",1, 0 ) > 0 ))

 

[melq64]

Thanks. The formula works but returns incorrect total count (1745) for zip codes beginning 0,1,2,3 when total count should be (2143)

Here's link to zip code list for you reference
https://www.dropbox.com/s/greiftnk417q18g/zip code list.xlsx?dl=0

 

[brawnystaff]

Hmm..download and gave the correct count. See link below...

https://drive.google.com/open?id=1Yhjy9TddBO9jx-y-N2msSuaYv3P6LHYk

 

[melq64]

Yes you right about the correct count. I was hoping you could replicate my issue with the full dataset. When I input the formula into powerpivot data model, the count total is different as mentioned earlier. I doubled checked that filters were turned off so not sure why incorrect count total...perhaps I'm missing something else?

Here's the actual formula in my datamodel with count total returned (1745)

TotalItemsForAMC:=CALCULATE(COUNTROWS ( 'DistProfile v1-3'),FILTER('DistProfile v1-3',If( LEFT('DistProfile v1-3'[Origin_Postcode],1)="0"||LEFT('DistProfile v1-3'[Origin_Postcode],1)="1"||LEFT('DistProfile v1-3'[Origin_Postcode],1)="2"||LEFT('DistProfile v1-3'[Origin_Postcode],1)="3",1, 0 ) > 0 ))

 

[melq64]

Sorry to bug you again brawnystaff but do you have any suggestions on what the issue could be regarding my last post? Would appreciate any feedback. Thanks

 

[brawnystaff]

Hard to say...although I note that your formatting in cells is inconsistent, as although it says "General" for all, some numbers appear to be formatted as Text (justification to left instead of right).

Perhaps another way to handle this is assuming that helper column "prefix" will be present, utilize that field to do the analysis (as well as shorten the formula). Using the prefix column as part of the table, the DAX formula would be as follows:

=CALCULATE(COUNTROWS ( Table1 ),FILTER( Table1,If(Table1[prefix]="0"||Table1[prefix]="1"||Table1[prefix]="2"||Table1[prefix]="3",1, 0 ) > 0 ))