Filter based on criteria

[Krishna1992]

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,

Product	Category
Juice	watermelon
Juice	Pinapple
Juice	papaya
Juice	Apple
soap	Hamam
soap	lifebouy
soap	cinthol
powder	sandal
powder	chintoor
powder	ponds

So my required output is:

Product	Category
Juice	watermelon
Juice	Pinapple
soap	Hamam
soap	lifebouy
powder	sandal

So I only required watermelon and pinapple when product = Juice.

Need to filter based on above criteria.

Thanks

[adityavighne]

@Krishna1992 

Add filters or Slicer for Product and Category and give multiple selection option.

regards,

aditya

[Krishna1992]

Hi @adityavighne ,

Thanks for your response..Is it possible to filter it from data itself, instead of visuals?

[az38]

Hi @Krishna1992 

whats scenario should be used? is it predefined filter? are you going to use a slicer?

you could try the simpliest way is to filter in visuals or to create a calculated table

Table = 
FILTER(ALL('Table');
'Table'[Product]="juice" && ('Table'[Category]="watermelon" || 'Table'[Category]="pinapple ")
)

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

[Krishna1992]

Hi @az38,

Can I filter these data from query editor itself instead of using DAX. I want data to be filtered before projecting in visuals. If I create DAX will that data be applicable for all visuals?

[az38]

@Krishna1992 

you could easily filter out suceedeed data in Power Query Editor like in Excel

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

[Krishna1992]

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.

Location	Product	Category
India	Juice	watermelon
India	Juice	Pinapple
India	soap	Hamam
India	soap	lifebouy
India	powder	sandal
US	Juice	Pinapple
US	Juice	papaya
US	soap	lifebouy
US	soap	cinthol
US	powder	sandal
US	powder	chintoor

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]

Hi @az38 ,

I will remodify the question,

Please try if you could try to understand,

Location	Category
India	watermelon
India	Pinapple
India	papaya
India	Apple
India	Apple
US	watermelon
US	Pinapple
US	papaya
US	Apple
US	Vinegar
US	Papaya

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]

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

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]

@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

[Krishna1992]

Hi @az38 ,

I was not a rule or selected by user, it was my requirement to select only required category.  User only requires specific category from each category.

[az38 · Accepted solution]

@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

[az38]

or create a DAX calculated column

=CONCATENATE([Location];[Category])

and filter out in the visual

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