Create new table with distinct values and filters

[Anonymous]

I want to create a new table that contain a distinct value of SKU. However, when I try with filters, the distinct values can not comply with the DAX code so I don't know where should I put the distinct code. Here is my code, basically I need the distinct values of SKU along with the brands and their descriptions. I also want to apply the filters where the SKU code must not start with "S", "O" and list of SKU with condition (brand<>blank()&&left(sku,1)="C-")

FILTER(SUMMARIZECOLUMNS(ItemPackage[SKU],ItemPackage[PackageDescription],ItemPackage[Brand Name]),LEFT(ItemPackage[SKU],1)<>"S"&&LEFT(ItemPackage[SKU],1)<>"O"&&(ItemPackage[Brand Name]<>BLANK()&&LEFT(ItemPackage[SKU],1)="C")))

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

I sent you a private message. Or do you have any suggestion for me to give you the sample data? Seems like I can't paste the data here

[amitchandak]

@Anonymous , Try like

SUMMARIZE(FILTER(ItemPackage, not(LEFT(ItemPackage[SKU],1) in {"S","O","C"}) && not(isblank(ItemPackage[SKU]))), ItemPackage[SKU],ItemPackage[PackageDescription],ItemPackage[Brand Name])

or

SUMMARIZE(FILTER(ItemPackage, not(LEFT(ItemPackage[SKU],1) in {"S","O","C"}) && not(isblank(ItemPackage[SKU]))), ItemPackage[SKU],ItemPackage[PackageDescription]."Brand Name",max(ItemPackage[Brand Name]))

[Anonymous]

Thank you for taking your time to answer my question but this will make the SKU appear twice/more than once (not distinct) if the descriptions are not same. I need the SKU to be distinct regardless of the description.

[amitchandak]

@Anonymous , try like

SUMMARIZE(FILTER(ItemPackage, not(LEFT(ItemPackage[SKU],1) in {"S","O","C"}) && not(isblank(ItemPackage[SKU]))), ItemPackage[SKU],"PackageDescription" ,max(ItemPackage[PackageDescription]),"Brand Name",max(ItemPackage[Brand Name]))

[Anonymous]

What if I need to add more condition that check 2 fields? The SKU that starts with C and brand that is blank, need to be removed. The additional condition is the same as this LEFT(ItemPackage[SKU],1)=2 && ItemPackage[Brand Name]<>BLANK() . I tried this code but turns out it delete all of the rows that starts with other codes. 

SUMMARIZE(FILTER(ItemPackage, not(LEFT(ItemPackage[SKU],1) in {"S","O","P","M"}) && (LEFT(ItemPackage[SKU],1) in {"C"} && ItemPackage[Brand Name]<>BLANK())&& NOT(CONTAINSSTRING(ItemPackage[PackageDescription],"Do not use")) && NOT(CONTAINSSTRING(ItemPackage[PackageDescription],"OBSOLETE"))), ItemPackage[SKU],"PackageDescription" ,max(ItemPackage[PackageDescription]),"Brand Name",max(ItemPackage[Brand Name]))

[amitchandak]

@Anonymous , Seem fine. Try with one change

SUMMARIZE(FILTER(ItemPackage, not(LEFT(ItemPackage[SKU],1) in {"S","O","P","M"}) && (LEFT(ItemPackage[SKU],1) in {"C"} && not(isblank(ItemPackage[Brand Name])))&& NOT(CONTAINSSTRING(ItemPackage[PackageDescription],"Do not use")) && NOT(CONTAINSSTRING(ItemPackage[PackageDescription],"OBSOLETE"))), ItemPackage[SKU],"PackageDescription" ,max(ItemPackage[PackageDescription]),"Brand Name",max(ItemPackage[Brand Name]))

Remove filter one by one check which one is causing this

[Anonymous]

Still. I'm supposed to have more than 9000 rows and now only left with 1000 rows. Other codes are removed like SKU that starts with B