Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
monak1
Helper I
Helper I

Country-Category Slicer Interaction Issue in Power BI: Graph Disappears when Changing Countries

I have a slicer country (from Country table ) on top of my report which affects all the graphs. I have a donut chart where I kept a slicer for category (category table). This slicer affects only donut chart and none other visuals . Country (from Country table ) slicer which affects all graphs has countries like India, USA etc . In terms of category , every country has unique category so India has for example categories such as A, B , C and USA has categories D , E, F etc. please note that my donut chart has details such as products as slices(legend) and count of item number as 'values' . Now when the country is selected as India, I choose A in category and pie chart shows information of category A and when I change to category B , it accordingly shows the information. But when I change the country from India to USA, the category slicer has 'A' selected but my pie chart disappears because of obvious reason that 'A' category is not available for USA country so only when I go to category slicer and choose a category again specific to the chosen country (i.e. USA) , then I see the graph appears again and shows correct information. This situation when a user changes a country and category slicer still showing category related to previous country and graph disappears until the users again selects a category related to selected country is not ideal . please note that my country and category table are dimensions ( one side of relationships) which are connected to fact table -product table (many side of relationship). please note that product table has also a country column but it does not have category column.  Could you please provide any solution to this unique problem ?

4 REPLIES 4
Mrxiang
Helper II
Helper II

Based on your description,  it seems like the issue is that the category slicer is not updating based on the selected country.  One solution to this  problem is to create a measure that filters the category slicer based on the selected country.

First, create a measure that returns the selected country from the slicer:

Selected Country = SELECTEDVALUE(Country[Country])
Next, create a measure that filters the category slicer based on the selected country:

Filtered Categories =
VAR SelectedCountry = [Selected Country]
RETURN

FILTER('Category', 'Category'[Country] = SelectedCountry)
Finally,  use the "Filtered Categories" measure as the source for your category slicer.  This should ensure that the category  slicer only shows categories that are available for the selected country,  and should prevent the issue of the pie chart disappearing when the country is changed.

thanks for your answer but your solution is not correct. we can not use a measure in slicers in Power BI. 

v-stephen-msft
Community Support
Community Support

Hi @monak1 , 

 

The issue you are facing is probably due to the slicer interaction between the country and category slicers. When you change the country slicer, the category slicer still shows the previously selected category, which may not be available for the newly selected country. This causes the pie chart to disappear until you select a category that is available for the newly selected country.

 

One solution to this problem is to use a disconnected table for the category slicer. A disconnected table is a table that is not connected to any other table in the data model. You can create a disconnected table for the category slicer that contains all the categories for all the countries. This table will not be affected by the country slicer, and will always show all the categories.

To create a disconnected table, you can use the "New Table" option in the "Modeling" tab of the Power BI Desktop. You can create a table with two columns, one for the category and one for the country. You can then populate this table with all the categories for all the countries.

 

Once you have created the disconnected table, you can use it as the source for the category slicer. You can then use the "SelectedValue" function in the DAX formula for the pie chart to filter the data based on the selected category and country. The "SelectedValue" function returns the selected value from a slicer or a filter, and can be used to filter data based on the selected value.

Here is an example DAX formula for the pie chart:

 

Sales by Category = 
CALCULATE(SUM(Sales[Revenue]), 
    FILTER(Sales, 
        Sales[Category] = SELECTEDVALUE(DisconnectedTable[Category]) && 
        Sales[Country] = SELECTEDVALUE(CountryTable[Country])
    )
)

 

In this formula, "DisconnectedTable" is the name of the disconnected table for the category slicer, and "CountryTable" is the name of the table for the country slicer. The formula calculates the sum of revenue for the selected category and country, using the "FILTER" function to filter the data based on the selected values from the slicers.

   

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.       

thanks a lot for your solutions. you seem to have understood my problem.  I tried to implement your suggestion However,  I am still facing issues. After creating a disconnected table and creating a measure for donut chart and using category slicer from disconnected table as per your suggestion, I can see all the categories irrespective of whichever country is selected in slicer. so in my slicer, I see for example category A B C (applicable for India) and cateogries D E F (applicable for USA) even if country slicer has 'India' selected. so when I select country A from category slicer, I see the graph which is fine but when I change country from India to USA, I still see category A selected and my graph disappears and I have again go and select another cateogry related to 'USA' and then I see the graph again. so with your solution, the only thing which changed is, I get to see all categories  in categorys slicer irrespecitve of country selected in country slicers. what I wanted was to ensure that my category slicer is updated according to country slicers( applicable to respecitve countries) and also my donut chart does not disappear (for this, one category related to selected country should get selected while changing the slicer) . please let me know if this is possible. thanks in advance

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors