Fetch Count of existence of substring from Cell Values in CSV file - Power BI Desktop

Question

am having a CSV file with a column name MFileName with values

1500_MM_19876_USA_TEST_TRXE
1800_RR_12960_US1_TEST_TRAX
1501_MM_19876_US1_TEST_TRXE
1802_RR_12960_USA_TEST_TRAX
1501_MM_19876_US1_TEST_TRXE
1803_RR_12960_USA_TEST_TRAX
1804_RR_12960_US1_TEST_TRAX

so, my requirement is show in a slicer, that, the USA ==== 3 && US1 ==== 4

i loaded this csv file into powerbi desktop and wanting to create a report/slicer . Am stuck at what DAX query need to be applied in order to get : want to fetch 1) how many records exist having "USA" as substring 2) how many records exist having "US1" as substring

I tried with some DAX queryies but its failed.i created a new column from the power query editor and tried to wrote the query.

USAcountcoulmn=IF(FIND("USA",table1(MfILENAME),1,0) >=1,1,0)

now i tried with another query :

USACountctual = SEARCH("USA",Table1[MFILENAME],,0)

as a result it returns , 0 or 1 in the cell values. But , how can i get the total number of cells which is having a substring(text) "USA" present in the column "MFILENAME" ? in the slicer, i should get the value as 3 for "USA" and 4 for "US1"

Answer 1

Try this measure

USACountctual = 
    COUNTAX(
        Table1 ;
        IFERROR( SEARCH( "US1" ; Table1[MFILENAME] ) ; BLANK() )
    )

Answer 2

Just wanted to share the Power Query approach that I believe is just easier in this case.

Create a new column, you can even use "Column for examples" to easily generate the M for you.

This way you can even use the new column, rename it as you will, and use it as category in your report.

EDIT:

In your report just drag any of the 2 columns and set it as a COUNT, since both columns are TEXT that should already be the default summarization if I'm not mistaken or create a new measure.

File Count = COUNTROWS('Table 1')