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

[KaySunset]

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

[shwet]

Hello, I have similar issue. I want to filter one dim table with the other. Ex. If one Country is selected in one slicer, Customers belongs to that country only should be populated in Customer Slicers. 
Data source is SSAS Cube data, so I cannot modify the relationship. Please suggest some workaround.

TIA

[v-yangliu-msft · Accepted solution]

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:

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.

[KaySunset]

This works as a charm, thank you!

[v-yangliu-msft]

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.

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.

[KaySunset]

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]

Hi  @KaySunset ,

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

I created some data:

Invoice table：

Customer table：

Product table：

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.

3. Result:

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.

[KaySunset]

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