Visual Suggestion

[kpl_999]

In the attached file, there is a visual which I need to create in Power BI. The problem is the individual is getting filtered by country and category. I can create individual visuals to filter them individually in the Dax statement but the problem is when the new country will come in the dataset then this will fail and automatically visuals will not be filtered or created. I will have to create the visual for a particular country each time a new country is added.

Any alternatives?

Regards,

Kaushlendra Mishra

www.powerbitalks.com

[v-lili6-msft]

hi  @kpl_999 

Why when the new country will come in the dataset then this will fail and automatically visuals will not be filtered or created?

If you drag country field into visual, it should be updated after dataset refresh.

Could you please share your sample pbix file for us have a test?

Regards,

Lin

[AllisonKennedy]

@kpl_999
sorry but I don't understand your question, did you mean to attach a sample file with your data model?

What does your data table look like? If you have the data model set up correctly you shouldn't need to create a new measure for each country, just need a DimCountry table perhaps to handle this?

[pranit828]

Hi @kpl_999 

I would write two DAX formulas for both as one will serve for Country and the other for category.

It will be better if you provide your table structure with sample data of all the columns used in the screenshot. this helps you get the exact solution rather than multiple replies asking for different information about data.

So, I am not sure how you are geting all the columns in the report. Considering they are all in rows for each country and categorywise and is calculated using a measure named measure_x and average is required for display.

_country_level = Calculate(AVERAGEX(table,table[measure_x]), ALLEXCEPT(table,table[country]))

_category_level = Calculate(AVERAGEX(table,table[measure_y]), ALLEXCEPT(table,table[category]))

If required, you may have to create a calculated field for each column and use SUMX, MAXX, AVERAGEX as required.

By using the above calculation you will not have to worry about new country or category.