Forum Discussion

KaySunset's avatar
KaySunset
Helper II
5 years ago
Solved

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
KaySunset
Helper II
5 years ago

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