topic Re: Create new table with distinct values and filters in DAX Commands and Tips

Create new table with distinct values and filters

Anonymous — Thu, 05 Nov 2020 03:12:50 GMT

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")))

Re: Create new table with distinct values and filters

amitchandak — Thu, 05 Nov 2020 03:22:24 GMT

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])

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]))

Re: Create new table with distinct values and filters

Anonymous — Thu, 05 Nov 2020 03:41:34 GMT

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.

Re: Create new table with distinct values and filters

amitchandak — Thu, 05 Nov 2020 06:13:05 GMT

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]))

Re: Create new table with distinct values and filters

Anonymous — Thu, 05 Nov 2020 07:59:27 GMT

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]))

Re: Create new table with distinct values and filters

amitchandak — Thu, 05 Nov 2020 08:51:03 GMT

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

Re: Create new table with distinct values and filters

Anonymous — Thu, 05 Nov 2020 09:04:17 GMT

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

Re: Create new table with distinct values and filters

amitchandak — Fri, 06 Nov 2020 02:13:39 GMT

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

Re: Create new table with distinct values and filters

Anonymous — Fri, 06 Nov 2020 04:47:35 GMT

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