cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How to clean filter for unused values

We have an OLAP SSAS cube with a sales fact table and an employee dimension table.

 

The employee table contains all company employees.

 

The sales table contains

UserCode ... who created the sale order

SalesmanCode ... who manages the customer

DriverCode ... who delivered the order

...

 

So we define three dimensions:

Dim_User linking sales table (UserCode) to employee table (EmployeeCode)

Dim_Salesman linking sales table (SalesmanCode) to employee table (EmployeeCode)

Dim_Driver linking sales table (DriverCode) to employee table (EmployeeCode)

 

Everything works as expected, but our PowerBi users are faced with a hugue list of employees (the whole table containing +200 rows) every time they want to use a filter on any of the three dimensions.

Obviously each dimension has data from the fact table only for a subset of the employees.

 

Ideally we would like to have on the Filter list only the values with associated data, so our PowerBi users would see just a short list.

 

Is this possible?

 

As a workaround we would need to create three diferent employee tables, but we would like to avoid that Smiley Frustrated

9 REPLIES 9
Highlighted
Resolver I
Resolver I

Re: How to clean filter for unused values

Hi,

 

Sounds like you might need row level security.. have a look here 

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

 

Dave

Highlighted
Helper I
Helper I

Re: How to clean filter for unused values

As far as I understand row-level security it will not provide the needed functionality.

 

Row-level security allow to restrict wich user can access wich data. So PowerBi user A can see only some specific values while user B can see another subset of the values.

 

But this is not what we are trying to obtain.

 

User A must be able to see alla data. User B also must be able to see all data.

 

When any of them looks a PowerBi chart where the SalesmanCode dimension is used we want all sales data visible ... but we want that the filter option shows only the values for which there is some fact data available, not all employee codes.

 

Let's put it to the maximum. There is only one sale, created obviously for only one Salesman, the fact table has just one row. The filter option still shows 200 possible values (all employees of the company). We would like the filter option showing only one value (the Salesman that created the sale).

 

Of course, maybe I don't fully understand row-level security 😞

Highlighted
Helper I
Helper I

Re: How to clean filter for unused values

@DeepButi unless I ammissing something why can't  you use just the User_Code field in the Sales_table as filter?

Are you bringing any other info from the User_Code table? 

 

Highlighted
Helper I
Helper I

Re: How to clean filter for unused values

@Bordalos

 

We use the User_code field on the filter. And it works.

 

The problem is, there is data on the fact table for just a few User_code values. But the filter shows all user_code values.

 

In the fact_table there is data for say 5 diferent User_code values, but there are more than 200 diferent values on the filter list. so selecting the one you are interested in is extremely annoying. The filter itself becomes useless because there is too many values with no data associated.

 

 

Highlighted
Helper I
Helper I

Re: How to clean filter for unused values

Sorry, I didn't read your post carefully. The User_code in the fact table is not available as a filter, it's a key to the related dimension on the cube, so it's not a measure. Only measures are available as data.

Highlighted
Helper I
Helper I

Re: How to clean filter for unused values

hi @DeepButi

 

if  [User_code] is not available in the table how are you doing the relationship between the slicer and the fatc_table?

Highlighted
Helper I
Helper I

Re: How to clean filter for unused values

Hi @Bordalos

yes, of course the code is in the fact_table. But it is not a measure, so not available as data. It is an OLAP cube, the fields used as keys to the dimension tables are not measures, so not visible in the fact_table.

Highlighted
Helper I
Helper I

Re: How to clean filter for unused values

@DeepButi I might be missig something but I can't see how you conecting the Slicer with the fact table.

Maybe you can  share a screenshot of the relationship page.

Highlighted
Helper I
Helper I

Re: How to clean filter for unused values

We are using a live connection to an OLAP SSAS cube. No relationship defined on PowerBI, everything comes up already defined from the cube itself.

 

There is no relationship page available on PowerBi.

 

I can annex the cube relationship definition ... although I guess you will not find what are you looking for there ... (only two of the fact table codes are shown)

 

Cube.jpg

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors