Cannot do "AND" conditions with Selectors in Power BI

[alex_bdo]

Given a table with the following structure:

Table 1

Rule ID	TYPE	VALUE
1	Mars	4
2	Venus	15
3	Mars	15
4	Mars	7
5	Venus	8

 And a second table with a 1 to Many relationship to Table 1 (with Rule ID being the foreign key)

 Table 2

RULEID	OFFERID
1	Offer 1
4	Offer 1
2	Offer 2
4	Offer 2
2	Offer 3
2	Offer 4

We would like to put multiple selectors on a report so a user can choose to view all "OFFERIDs that have a TYPE ‘Mars’ with a VALUE  ‘7’ AND  have a TYPE ‘Venus’ with a VALUE ‘15’"

The result of this Query will be ‘Offer 2’, as it is the only OFFERID which has a RULEID of both 4 AND 2.

We have tried the following solution:

Step 1: Create two datasets, (1) all rules of type Mars, (2) all rules of type Venus;

Step 2: put selectors on the two datasets. 

Step 3: Create a third dataset which is a union of the two datasets

Issue: Power BI does not support union of filtered datasets

The temporary solution that we put in is to use OR  functionality (using a hierarchy slicer), which would return all rows that have RULEIDs of 4 OR 2. Or in this case, it would return Offer 1, Offer 2, Offer 3, and Offer 4.  The user than needs to interpret the data themselves to determine which offers have been RULEID 4 and 2.

Is there any other way to satisfy this requirement?

[v-qiuyu-msft · Accepted solution]

Hi @alex_bdo,

You can create a new table (named Table1 copy) with the same columns of Table1. Then create a measure below: 

Measure =
VAR id_list =
    CALCULATETABLE ( VALUES ( Table1[Rule ID] ), ALLSELECTED ( Table1 ) )
VAR id_list2 =
    CALCULATETABLE ( VALUES ( 'value'[Rule ID] ), ALLSELECTED ( 'value' ) )
RETURN
    CONCATENATEX (
        INTERSECT (
            CALCULATETABLE (
                VALUES ( Table2[OFFERID] ),
                FILTER ( ALL ( Table2 ), [RULEID] IN id_list )
            ),
            CALCULATETABLE (
                VALUES ( Table2[OFFERID] ),
                FILTER ( ALL ( Table2 ), [RULEID] IN id_list2 )
            )
        ),
        [OFFERID],
        ","
    )

Best Regards,
Qiuyun Yu 

[alex_bdo]

Thanks, this solved the problem that we were having.  We will need to modify it slightly to output a table instead of a comma-delimited string.