Filter Dimension Two by column in Dimension One

[cturner]

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.

[Eric_Zhang]

---

@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.

---

@cturner

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.

---

@cturner

Then create a measure in the same way with type column.

isExistsInTable1 = IF(LASTNONBLANK(Table2[type],"") in VALUES(Table1[type]),1,BLANK())

[cturner]

@Eric_Zhang thanks so much, this is very helpful.

Is there a similar approach that would work for multiple selections/all in the slicer?

[Eric_Zhang]

---

@cturner wrote:

@Eric_Zhang thanks so much, this is very helpful.

Is there a similar approach that would work for multiple selections/all in the slicer?

---

@cturner

The measure shall works for multiple sections in a slicer, have you got any problem?