Filtering using the same table as base

[Anonymous]

Hello To all

I'm trying to show in a report info that should be filter by two values that are in the same table, if I do it as it is I get zero results (which is correct).

How can I do this?

My table A is:

wid  value

1       2

2       8

3       3

4       5

5       1

Table B

id wid value type
1   1     2        1

2   1     4        2

3   2     5        1

4   3     3        1

5   3     9        2

6   4     8        1

They are linked by wid

and I what to have the possibility to shoe the values filter by type.

if I use type 1 in one filter and type 2 on the other

it should dispay the wid 1 and values 2 and 4.

Should I make auxiliary tables for each type but dinamic ones?

Thanks

[v-cherch-msft]

Hi @Anonymous

It seems it's better to make auxiliary tables .

Regards,

Cherie

[Anonymous]

Hello @v-cherch-msft


Just did. I breaked the table B in two

Table B1

id wid value type
1   1     2        1

3   2     5        1

4   3     3        1

6   4     8        1

Table B2

id wid value type

2   1     4        2

5   3     9        2

by using FILTER with type.

But now if I link them to table A will I not get the same result? Or am I beeing a newbie.

Thanks

[v-cherch-msft]

Hi @Anonymous

I reviewed your question and I think i cannot fully understand it. Why not use wid column in TableB as slicer to get it as below picture? Using type columns as slicers seems more complicated.

Regards,

Cherie

[Anonymous]

Hello @v-cherch-msft

I cannot use wid because in fact what I need to display is the value of Table A filter by both types.

I need to get the wid that as both type 1 and type 2 and use that to get the value of Table A.

In SQL seems more easy... 🙂

Regards

[v-cherch-msft]

Hi @Anonymous

Check below picture if it matches your request. Add two slicers and add a measure for Table A. Here is the sample file.

Regards,

Cherie

[Anonymous]

Hello @v-cherch-msft

Getting there. 🙂

But now (and I believe is due to the measure) I'm not getting the desired results because I have more that type 1 and 2. Can this be the case?

Still digging the formulas but not yet arrived to the problem.

Regards

[v-cherch-msft]

Hi @Anonymous

Just check your relationship and use 'Both' as cross filter direction. Then try below measure. It would be better to show more about your data sample for me to check it.

Measure =
IF (
    COUNTX (
        VALUES ( TableB[type] ),
        IF (
            SELECTEDVALUE ( SlicerA[type] ) = MAX ( TableB[type] )
                || SELECTEDVALUE ( SlicerB[type] ) = MAX ( TableB[type] ),
            1
        )
    )
        >= 2,
    1
)

Regards,

Cherie