Forum Discussion
Helper IFilter Dimension Two by column in Dimension One
I've been googling this today, and can't quite get clarity on the correct approach:
I have a star schema database with multiple facts and multiple dimensions. Each fact has foreign keys for each dimension, corresponding to primary keys in the dimension table. We group data across the various fact tables based only on these dimensions.
We've been asked to limit the contents of the slicer for dimension 2 by our selection of the slicer in dimension 1. We can't add a FK for dimension 1 to dimension 2 to join them as it creates ambiguity and powerbi refuses to join them.
for dimension 1, we have id, business_name, and type
for dimension 2, we have columns id, product_name, and type
column 1 is whole number
column 2 and 3 are text
only column 1 is unique in both tables.
without a relationship between the tables, whats the proper dax pattern for returning only the rows in dimension 2 that share the same value for column type as the rows selected by the slicer for dimension 1?
Thanks.
5 Replies
Eric_ZhangMicrosoft Employee
cturner wrote:
I've been googling this today, and can't quite get clarity on the correct approach:
I have a star schema database with multiple facts and multiple dimensions. Each fact has foreign keys for each dimension, corresponding to primary keys in the dimension table. We group data across the various fact tables based only on these dimensions.We've been asked to limit the contents of the slicer for dimension 2 by our selection of the slicer in dimension 1. We can't add a FK for dimension 1 to dimension 2 to join them as it creates ambiguity and powerbi refuses to join them.
for dimension 1, we have id, business_name, and type
for dimension 2, we have columns id, product_name, and type
column 1 is whole number
column 2 and 3 are text
only column 1 is unique in both tables.
without a relationship between the tables, whats the proper dax pattern for returning only the rows in dimension 2 that share the same value for column type as the rows selected by the slicer for dimension 1?
Thanks.
You may need a measure as below. See the attached pbix file.
isExistsInTable1 = IF(MAX(Table2[id]) in VALUES(Table1[id]),1,BLANK())
- cturner
This is helpful, and may contain the seeds of a working approach. However, I don't want to use ID for the condition. I want to use type:
Table 1
id,business name,type
1,bname1,type1
2,bname2,type2
3,bname3,type1
4,bname4,type2
Table 2
id,productname,type
1,pname1,type1
2,pname2,type2
3,pname3,type1
4,pname4,type2
Slicer1 for type in table 1 contains the values type1,type 2.
Slicer2 for productname in table 2 contains the values pname1-pname4.
I want the type selected in slicer 1 to determine the contents of slicer 2
type 1 -> pname1,pname3
type 2 -> pname2,pname4
Thanks.
- Eric_Zhang
cturner wrote:
This is helpful, and may contain the seeds of a working approach. However, I don't want to use ID for the condition. I want to use type:
Table 1
id,business name,type
1,bname1,type1
2,bname2,type2
3,bname3,type1
4,bname4,type2
Table 2
id,productname,type
1,pname1,type1
2,pname2,type2
3,pname3,type1
4,pname4,type2
Slicer1 for type in table 1 contains the values type1,type 2.
Slicer2 for productname in table 2 contains the values pname1-pname4.
I want the type selected in slicer 1 to determine the contents of slicer 2
type 1 -> pname1,pname3
type 2 -> pname2,pname4
Thanks.
Then create a measure in the same way with type column.
isExistsInTable1 = IF(LASTNONBLANK(Table2[type],"") in VALUES(Table1[type]),1,BLANK())
