Forum Discussion

neko_mai's avatar
neko_mai
New Member
3 years ago
Solved

Different filter on same table depending on external selection

Hi PowerBI masters, 

I have a dashboard showing relation over different cities and the sales they had on different products. 

 

The structure of the dashboard is an interactive windows for the cities and a table showing the products and the quantity of them sold. From standart requirement, the table only shows the products that have 100 orders or more. Monthly we send the reports in pdf to each city manager so they can see how everything is going (so, this explain the city button, it's easier to pick a city, export to pdf, pick another one... an so on).

 

Now the thing: 

There are some cities that have asked if it's possible to have all products shown for them, regardless the quantity sold, cause they are "little" ones. 

I have tried to select the little cities and erase the filter on it's table, but as it's a shared item I found out that the filter is erased on all of them. 
¿Is there any way to, within the same table, have this filter of minimum quantity behaving different (2 ways, one with minimum of 100 orders and the other one without minimum) depending of the city I'm looking?

Actually the only way I have found to have everything send correctly is to convert to pdf first the big cities, erase the filter and convert the little ones (without saving or setting again the filter if I save the file incorrectly). 

 

Ah, and I have no option to make two different reports (one for big cities and another for little ones) due to... well... rules inside the company 🙄

 

Thanks to all.

KR.

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi neko_mai 

     

    You can add a table "Little Group Cities" which contains all cities that have asked to have all products shown for them. Just like below. Keep it disconnected with the Data table. 

     

    Then create the following measure and use it on the table visual as a filter. Set it to show items when value is 1. It will check the selected city first. If the city is in "Little Group Cities" table, return 1 for all products. If the city is not "little" one, return 1 when quantity is greater than or equal to 100. 

    100 or more flag =
IF (
    SELECTEDVALUE ( 'Table'[City] ) IN VALUES ( 'Little Group Cities'[City] ),
    1,
    IF ( SUM ( 'Table'[Quantity] ) >= 100, 1, 0 )
)

     

    I have attached the sample pbix at bottom. Hope it helps. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi neko_mai 

     

    You can add a table "Little Group Cities" which contains all cities that have asked to have all products shown for them. Just like below. Keep it disconnected with the Data table. 

     

    Then create the following measure and use it on the table visual as a filter. Set it to show items when value is 1. It will check the selected city first. If the city is in "Little Group Cities" table, return 1 for all products. If the city is not "little" one, return 1 when quantity is greater than or equal to 100. 

    100 or more flag =
IF (
    SELECTEDVALUE ( 'Table'[City] ) IN VALUES ( 'Little Group Cities'[City] ),
    1,
    IF ( SUM ( 'Table'[Quantity] ) >= 100, 1, 0 )
)

     

    I have attached the sample pbix at bottom. Hope it helps. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.