Forum Discussion
Filter dimensional data based on other dimension value without using facts (in a star schema)
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.
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.
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?
Thanks in advance!
Kathrin
