Expand measure with exclusion of some entries

[Anonymous]

Dear All,

I have written the following measure:

Number of delivery customer numbers =

VAR _SummTable =

    SUMMARIZE ( 'Tbl_Sales', 'Tbl_customer'[Deliverycustnr] )

VAR Result =

    SUMX ( _SummTable, 1 )      

RETURN

    Result

The result is showing the total number of delivery customer numbers for a customer. The table 'Tbl_customer' also contains a field called  'deliverycustname' (Tbl_customer'[deliverycustname]. Some exceptions for the description exist. In addition to the normal descriptions, it gives three type of descriptions which I want to exclude from the result in the measure. My question is how I can add these exclusion to the measure? The three types of descriptions are:

* Descriptions starting with PP_

* Descriptions starting with BX_

* Descriptions starting with PD_

Delivery customer numbers with such descriptions of delivery name should be excluded from the count in the measure. 

A simplified table situation to illustrate:

The current measure does count 5 different delivery customer numbers. In the new situation three records should be excluded, so the final result of the new measure should be 2 different delivery customer numbers.

Thanks in advance for any suggestion to improve the measure. 

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

count1 =
var _table1=
FILTER(
    'Tbl_customer',       SEARCH("PP_",'Tbl_customer'[Deliveryname],1,BLANK())=1&&CONTAINSSTRING('Tbl_customer'[Deliveryname],"PP_")=TRUE() ||       SEARCH("BX_",'Tbl_customer'[Deliveryname],1,BLANK())=1&&CONTAINSSTRING('Tbl_customer'[Deliveryname],"BX_")=TRUE() ||    SEARCH("PD_",'Tbl_customer'[Deliveryname],1,BLANK())=1&&CONTAINSSTRING('Tbl_customer'[Deliveryname],"PD_")=TRUE() )
return
IF(
    MAX('Tbl_customer'[Deliveryname]) in SELECTCOLUMNS(_table1,"flag",[Deliveryname]),0,1)

count2 =
SUMX(ALL(Tbl_customer),[count1])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Thanks a lot for your suggestion and it works quite well. I only miss a sum of the total number, so it just points out as '1''. In my original measure I make use of a SUMX, which rings me to a total number of delivery customer names for a specific customer . With the proposed measure it just turns out to 1 delivery customer name.

Thanks in adavcen for any additional thought to get this resolved.