Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
Add filters or Slicer for Product and Category and give multiple selection option.
regards,
aditya
Hi @adityavighne ,
Thanks for your response..Is it possible to filter it from data itself, instead of visuals?
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
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?
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
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
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.
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
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.
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
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.
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
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