Forum Discussion

Krishna1992's avatar
Krishna1992
Helper I
6 years ago
Solved

Filter based on criteria

Hello everyone,   I have 2 columns in my table, one is category and other is product.   I need to select/filter only required category based on product.   Something like below table, Produc...
Need Help
  • az38's avatar
    az38
    6 years ago

    Krishna1992 

    could you do it manually?

    go to the Power Query Editor pick your table, add Custom column like

    = Text.Combine({[Location], "-", [Category]})

    then filter out all you don't need like in Excel

    do not hesitate to give a kudo to useful posts and mark solutions as solution

Krishna1992's avatar
Krishna1992
Helper I
6 years ago

I cannot filter in power query because they will not make sense. I will change things up slightly,

 

If we add location column to it. Ex.

LocationProductCategory
IndiaJuicewatermelon
IndiaJuicePinapple
IndiasoapHamam
Indiasoaplifebouy
Indiapowdersandal
USJuicePinapple
USJuicepapaya
USsoaplifebouy
USsoapcinthol
USpowdersandal
USpowderchintoor

 

If I filter category, other location will also filter out, can I do anything in this in query editor. or I need to do DAX?

 

Thanks

 

 

Krishna1992's avatar
Krishna1992
Helper I
6 years ago

Hi az38 ,

 

I will remodify the question,

 

Please try if you could try to understand,

 

LocationCategory
Indiawatermelon
IndiaPinapple
Indiapapaya
IndiaApple
IndiaApple
USwatermelon
USPinapple
USpapaya
USApple
USVinegar
USPapaya

 

I need to select category based on location, Eg. India watermelon and pinapple, and for US I only need apple, vinegar.This is requirement. If you could try to through me a solution. Thanks in advance.

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Krishna1992 

    lets start step-by-step.

    whats your data source? how it looks like? Table "Lcation-Category" is the only data source? How can we define that for India it should be only watermelon and pinapple?

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • Krishna1992's avatar
      Krishna1992
      Helper I
      6 years ago

      Hi az38 ,

      I need to filter category on different location. Which means Every location may have same category or different category.

      But I need to select required category for specific location. Ex. if it is India I need to select certain category, if it is US it need to select certain category.

       

      Eg.

      India - I need watermelon, apple

      US - I need vinegar, watermelon, pinapple.

       

      Likewise I need to multiple location. Only DAX is possible or can I do in query editor. Thanks for your response.

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Krishna1992 

        how can we understand that for India doesn't need vinegar, for USA - apple? is there a rule or it should be selected by user?

        do not hesitate to give a kudo to useful posts and mark solutions as solution