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

Filter dimensional data based on other dimension value without using facts (in a star schema)

Hello everyone,


I have an (I think) complex issue with filtering a table/matrix that does not contain fact data. My model is a star schema with fact table 'invoice' and dimensional tables 'product','customer', 'tenant' & 'date'. My requirement is to set a slicer that filters all tables/matrixes/visuals in my PBI Dashboard for a certain tenant. My problem is, that my product and customer dimensions also contain certain metrics and I have tables/matrixes in place, that only hold dimensional data of one of them, so e.g. a table that only contains "ProductNumber", "ProductName" and "PurchasePrice" from dimension 'DimProduct'. As there's no reference to facts, I cannot filter these tables/matrixes with the [TenantID] column in the Tenant-Dimension.

I have established an inactive bidirectional relationship between product-tenant and customer-tenant, which I cannot activate as this would destroy the star schema. A slicer based on a column with following logic filters my product-data-only-table as wished, but this does not include the customer data: ProductDataFilter = LOOKUPVALUE(Tenant[Name],Tenant[TenantID],'Product'[TenantID]) .

Workarounds with multiple slicers or including facts into the table and then hide them are not what I'm looking for, I am hoping you guys can find a smooth solution to establish a single slicer that filters all my tables/matrixes/visuals for a certain tenant, regardless if there's a fact column used or not?


Hope my explanations are sufficient.


Many thanks and best regards,

Kathrin

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @KaySunset ,

You can try the following formula:

Here are the steps you can follow:

1. Create measure.

Measure =
var _selected=SELECTCOLUMNS(ALLSELECTED(Tenant),"1",[tenant])
return
IF(MAX('Customer'[Tenant]) in _selected||MAX('Product'[tenant]) in _selected,1,0)

2. Result:

vyangliumsft_0-1624435854273.png

 

Best Regards,

Liu Yang

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

View solution in original post

6 REPLIES 6
v-yangliu-msft
Community Support
Community Support

Hi  @KaySunset ,

You can try the following formula:

Here are the steps you can follow:

1. Create measure.

Measure =
var _selected=SELECTCOLUMNS(ALLSELECTED(Tenant),"1",[tenant])
return
IF(MAX('Customer'[Tenant]) in _selected||MAX('Product'[tenant]) in _selected,1,0)

2. Result:

vyangliumsft_0-1624435854273.png

 

Best Regards,

Liu Yang

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

This works as a charm, thank you!

v-yangliu-msft
Community Support
Community Support

Hi  @KaySunset ,

Here are the steps you can follow:

1. Create meausre.

 

all flag =
var _selected=ALLSELECTED(Tenant[tenant])
var _product=SELECTCOLUMNS(FILTER('Product',[tenant] in _selected),"1",[product])
var _customer=SELECTCOLUMNS(FILTER('Customer',[tenant] in _selected),"1",[Customer])
return
IF(MAX('Product'[product]) in _product || MAX('Customer'[Customer]) in _customer,1,0)

 

2. Result.

vyangliumsft_0-1624007145966.png

Best Regards,

Liu Yang

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

Hi Liu Yang,

 

thanks again!

I still have two more issues though, to make it as complete as possible. Sorry to bother again.

 

1) It gets slighlty more complex, as my slicer value is a name column that is not referenced in the other tables. Thus, I need to filter for this name column, but the flag still needs to contain the reference to the ID-column. The problem should be seen in the newly attached file below.

I have tried to work with Lookupvalue() such as following, but this won't work:

all flag new =
var _selected=ALLSELECTED(Tenant[Name])
var _selectedID = LOOKUPVALUE(Tenant[TenantID],Tenant[Name],_selected)
var _product=SELECTCOLUMNS(FILTER('Product',[TenantID] in _selectedID),"1",[ProductID])
var _customer=SELECTCOLUMNS(FILTER('Customer',[TenantID] in _selectedID),"1",[CustomerID])
return
IF(MAX('Product'[ProductID]) in _product || MAX('Customer'[CustomerID]) in _customer,1,0)

 

 

2)  The new "all" flag only filters the customer data by use of the star schema. As the flag is a measure within the product dimension, by using this in the customer-only table I'd filter the customer-only-table (="Customer Table" in our model) for only facts, that are related to the customers (at least as far as I understand the star schema). There could be customers, however, who do not have any facts (=invoices) related to them within a certain time period. I would still want to display these customers in the customer-only table.

Do you think there's any way to establish this measure in my metrics-table, such that everyhting is filtered correctly? Thinking of it, this might simply not be possible, as I'll always somehow have to use the star schema. Thus, I'd go back to the two-flag system in each the customer table and the product table. But maybe you have another good idea to make my model less complex? 🙂

 

Thanks again!

Kathrin

 

PBIX File 

 

 

 

v-yangliu-msft
Community Support
Community Support

Hi  @KaySunset ,

You can use Flag as a marker to filter dimension data based on other dimension values

I created some data:

Invoice table:

vyangliumsft_0-1623916790911.png

Customer table:

vyangliumsft_1-1623916790913.png

Product table:

vyangliumsft_2-1623916790913.png

Here are the steps you can follow:

1. Create meausre.

Flag =
var _selected=ALLSELECTED(Customer[Customer])
var _product=SELECTCOLUMNS(FILTER(ALL(invoice),[customer] in _selected),"1",[product])
return
IF(MAX('Product'[product]) in _product,1,0)

2. Place [Flag] in Filter and set is=1, apply filter.

vyangliumsft_3-1623916790916.png

3. Result:

vyangliumsft_4-1623916790918.png

 

 

Best Regards,

Liu Yang

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

Hi Liu Yang,

 

many thanks for your response! Your solution works very well and I can use this as workaround, my problem still is a little bit more complex though.

I have a third dimension, Tenant, that needs to filter both other dimensions (even when no facts are used in a visual). There is a tenant reference in both dimension tables.

I can easily use your solution by creating a flag in each of the dimension (customer & product). As I want to enable self service with the dataset, however, I'd prefer an even simpler solution.

Is there any way, to filter all three tables shown in my PBIX (Fact table, Customer Table & Product Table) with only one measure/column in a single slicer?

PBIX 

Thanks in advance!

Kathrin

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors