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
DeepButi
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
Bordalos
Helper I
Helper I

@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? 

 

@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.

 

 

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.

hi @DeepButi

 

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

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.

@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.

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

davehus
Memorable Member
Memorable Member

Hi,

 

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

 

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

 

Dave

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 😞

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.