Slicer always set to the top country (RLS also included)

[joannasokolowsk]

Dear Colleagues,

I need your advice regarding slicer setp up for project where I also use row level security.

The case is as following - I created a dashboard where Leaders can see data for their countries, however there are few leaders who have 2 countries. I do not want to summarazie data for both countries so I added slicer and I would like this slicer to always select one country (could be according to alphabetical order), so those leaders who have only one country have their country selected, and the ones with more countries can change the selection (for them also one country already selected by default) . 

Thank you in advance!

Asia

[joannasokolowsk]

Hey,

It is almost about all visuals and in some cases there is a place only for one valuse (visual card).

I also have another issue bc it looks like I can't show both territories now - there is an error telling that desired outcome should includes only one value but includes 2 (2 countries on the list)

[v-yanjiang-msft]

Hi @joannasokolowsk ,

In your sample, as all tables are related with the Summary by country table, you can only set RLS with the Summary by country table like below:

Then the country slicer should also from the Summary by country table.

By test the slicer cannot be set to the specified value, you can sort it by ascending.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[joannasokolowsk]

Hey,

Thank you for the response! I already set up the RLS and it works! The only problem I have is the default selection on the slicer. I have some more statistics for each country so they should be displayed separately (and if there is no slicer selected then values are sum up together for both countries, which I do not want to do). 

[v-yanjiang-msft]

Hi @joannasokolowsk ,

Can you provide more information about which value will be sum up together for both countries? Is it a measure or column, you can put the value and country in the same visual, they should be displayed separately.

Best Regards,
Community Support Team _ kalyj

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

[joannasokolowsk]

 again thanks for the quick reply! Could you please share the solution/details for the solution you mentioned? 

[Anonymous]

I have given the detailed solution in my reply. There is nothing more to it. All the DAX codes and visual settings are mentioned in that post. Please refer to it.

[joannasokolowsk]

Hey! Thank you for detailed explanation! Let me share with you maybe more details with the project I have. 

I have relationships so Leaders data is in separated table:

I have 4 data sources as below:

-> Leaders ID's for RLS

-> Summary by country

-> Session attendance

-> Trainign status

All relationships are made based on Country so once Team Leader open dashboard on the power bi reporting server will see only data for  connected countries. 

Now, I want to create such dashboard where Team Leader with one country see it like this :

E.g. for Anna who is a Team Leader only for Hungary with slicer (Country unique) alrady marked for Hungary

While Leader who is connected with 2 countries (e.g. Tom who has Poland, Russia) will see Poland as selected by default but could also filter Russia

I hope it makes more sence now 🙂 

Asia

[Anonymous]

I think it is not possible to actually 'SELECT' something based on the user in a slicer. But, we can determine a default country for every user in a calculated column using some DAX expression and that column could be used to simulate this selection. Then using a toggle or slicer, we can give the option to the users to change the country.

[Anonymous]

I will show you one workaround to do this. I am using alphabetical order to pick the first country as the default one. But you could use any dax condition, may be like the highest sales value or something to set as the default country for every user.

Assume you have the following table.

Table Name: 'Users Country'

Add four calculated columns to this table using the following DAX codes.

AlphaCode = UNICODE('Users Country'[Country])

Default Country Code =
VAR X = 'Users Country'[User]
VAR Result =
    MINX (
        FILTER ( 'Users Country', 'Users Country'[User] = X ),
        'Users Country'[AlphaCode]
    )
RETURN
    Result

Slicer Country Name =
IF (
    'Users Country'[AlphaCode] = 'Users Country'[Default Country Code],
    'Users Country'[Country] & "(Default)",
    'Users Country'[Country]
)

Slicer Country Default =
IF (
    'Users Country'[AlphaCode] = 'Users Country'[Default Country Code],
    "Default",
    "Others"
)

Please note that the first column gets a numeric code and the second column takes the minimum of the numeric code to set a default country alphabetically. I agree that there is a caveat that if two countries have the same first letter for the same user, it won't give the result. But the point is, instead of using this alphabetical thing, you could use any DAX like Max sales or something like that to pick one default country. If you have a numerical country code, you could use that. Treat this as a toy example and not as a production-grade code.

The other two calculated columns are just some texts generated based on the default country.

Once you create all these four calculated columns, your table will look like this.

Based on this, you will be able to achieve the following through various visuals. I used a toggle button, just to try it, but you could use a slicer for that also.

I have created a measure just for illustration. In reality, you won't need this.

SelectedCountry = SELECTEDVALUE('Users Country'[Country])

The "User 5" has two countries. The "select Default Country" toggle is on. So the "Selected Country" measure is showing his default country. If you turn the toggle off, the user will be able to select any other country.

I don't know how many users have this kind of requirement. But you could try out something like this as we don't have a standard way to achieve this.

Given below is the setting that I have used for the toggle button.

You could use a slicer instead, the toggle button is a kind of UI/UX factor. Nothing to do with the functionality.