Forum Discussion
Filter table rows like web shop filters
Hi everyone,
i'm having a real frustrating experience with PowerBI these days, but i'll try to keep my feelings out of this :D.
Data:
I have three tables:
Table 1: ItemDescription
| ItemNumber | Description |
123 | Product A |
| 234 | Product B |
| 345 | Product C |
| 456 | Product D |
| 567 | Product E |
Table 2: ItemColor
| ItemNumber | Color |
| 123 | White |
| 123 | Yellow |
| 234 | Yellow |
| 234 | Green |
| 345 | Grey |
| 456 | Blue |
| 567 | Pink |
Table 3: ItemLanguage
| ItemNumber | Language |
| 123 | DE |
| 123 | EN |
| 234 | DE |
| 234 | CZ |
| 345 | EN |
| 456 | EN |
| 567 | EN |
As you can see in the data, one product can be in muttiple languages or colors.
Goal:
I'd like to be able to filter the products in table 1 by using slicers. the behaviour of the filters shoul dbe as follows:
- No filters → shows all
- Only filter Language (e.g. DE) → shows only rows that have DE
- Only filter Language (e.g. DE, EN) → shows only rows that have both DE & EN
- Only filter Color (e.g. Green) → shows only rows that have Green
- Only filter Color (e.g. Yellow, Green) → shows only rows that have both Yellow & Green
- Both filters → shown rows must match all selected filter values
Issue:
I tried many different approaches - with and without table connections, single or both filtering directions, DAX measure to calculate matches, etc. - but nothing behaves as described above. The best i could do was that the only caviat was that i HAVE to use all filters, but it should also work if i only use one filter. Basically like filtering in web shops work. I've already consulted AI but it just keeps telling that i'm doing it correctly and that it should work as desribed 😄
Please help!
First you'll need some new tables for use in the slicers. Create Color and Language tables which have the unique values from the relevant columns, and link them in one-to-many relationships with ItemColor and ItemLanguage respectively. Also create one-to-many relationships from ItemDescription to both ItemColor and ItemLanguage tables.
You can then create a measure like
Item is visible = VAR ColorIsVisible = IF( NOT ISFILTERED( Color[Color] ), 1, VAR SelectedColors = COUNTROWS( VALUES( Color[Color] ) ) VAR CurrentColors = COUNTROWS( VALUES( ItemColor[Color] ) ) RETURN IF( SelectedColors = CurrentColors ,1, 0 ) ) VAR LangIsVisible = IF( NOT ISFILTERED( 'Language'[Language] ), 1, VAR SelectedLangs = COUNTROWS( VALUES( 'Language'[Language] ) ) VAR CurrentLangs = COUNTROWS( VALUES( ItemLanguage[Language] ) ) RETURN IF( SelectedLangs = CurrentLangs, 1, 0 ) ) RETURN IF( ColorIsVisible && LangIsVisible, 1 )Put this measure as a visual level filter on a table or matrix with the product description, set to show only when the value is 1. See the attached PBIX for reference.
4 Replies
johnt75Super User
First you'll need some new tables for use in the slicers. Create Color and Language tables which have the unique values from the relevant columns, and link them in one-to-many relationships with ItemColor and ItemLanguage respectively. Also create one-to-many relationships from ItemDescription to both ItemColor and ItemLanguage tables.
You can then create a measure like
Item is visible = VAR ColorIsVisible = IF( NOT ISFILTERED( Color[Color] ), 1, VAR SelectedColors = COUNTROWS( VALUES( Color[Color] ) ) VAR CurrentColors = COUNTROWS( VALUES( ItemColor[Color] ) ) RETURN IF( SelectedColors = CurrentColors ,1, 0 ) ) VAR LangIsVisible = IF( NOT ISFILTERED( 'Language'[Language] ), 1, VAR SelectedLangs = COUNTROWS( VALUES( 'Language'[Language] ) ) VAR CurrentLangs = COUNTROWS( VALUES( ItemLanguage[Language] ) ) RETURN IF( SelectedLangs = CurrentLangs, 1, 0 ) ) RETURN IF( ColorIsVisible && LangIsVisible, 1 )Put this measure as a visual level filter on a table or matrix with the product description, set to show only when the value is 1. See the attached PBIX for reference.
Thank you very much for this! it works exactly as expected 🙂
- FBergamaschi
My solution is available here
https://drive.google.com/drive/folders/1ypfClEUWrGcNEdik4L3eSk4tbEeCBKqv?usp=sharing
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
This doesn't work as described. If i select all colors, it shows all results, while it should show none, because none of the items have all the colors.
