Compare multiple values selected in a slicer to an existing column

[Rishabh-Maini]

Hi,

I have two tables with a column 'ID'.  (something like this)

Table 1

ID
1
2
3
4
5
6
7
8
9

Table 2

ID

1

3

5

9

The requirement is:   Using table 1 as a slicer, I need to find out when multiple values are selected from the slicer, whether or not they exist in Table 2 and display a message in a card (for example: "message")

If a user selects values 4 and 7 from table 1, no message should be displayed.
If a user selects values 1, 5 and 7 from table 1, display "message" 

I tried using Selectedvalue, but that only allows to check for one value from the Table 1.

Dynamic header = 
VAR _values =
    VALUES ( 'Table 2'[ID] )
VAR _message = "Message"
RETURN
    IF ( SELECTEDVALUE ( 'Table 1'[ID] ) IN _values, _message, " " )

Here's what it looks like

But when i select multiple values from Table 1 (which are present in Table 2) the message disappears

Is there any DAX function that will help iterate through the selection from Table 1 and check if the values exist in the Table 2?



PS: I have over 100 values in total in the original file, so hardcoding is my last resort but I'd rather use a DAX function to sort this one.

I have attached a sample file here

Thank you!

[Rishabh-Maini]

Hi @OwenAuger,

Looks like I might a little more assistance.

There is another added requirement:  The message should only show if the user selects a certain date range (along with the requirement above).

So the message is displayed when: 
1) The ID is present in both the tables
AND
2) When the user selects a certain date range 

I'm unable to use the IF function to my advantage. Could you help me out, yet again? 
(not sure how to record/store the selected date range somehow)

Thank you!

[OwenAuger]

Hi @Rishabh-Maini 

No problem!

I'm sure that's possible 😉

Could you clarify how the date range is selected (eg slicer on date table column) and what condition is tested based on that date range?

[Rishabh-Maini]

Thanks for the quick reply @OwenAuger !

So I have a date column (from the main date table) which I use as a slicer. 

The message should show up only if the selected dates are within the month of July. Currently, there is no date column in either Table 1 or 2. Is it something that should be added?

The message is to be displayed only when these two separate conditions hold true: 

1) If the selected date is within July

AND
2) If the ID intersects between Table 1 and 2

I've attached a link to the sample file here, please let me know if this works or if you have any more questions.

Thank you! 

[OwenAuger]

Thanks @Rishabh-Maini !

A couple of questions:

1. In condition 1: Is it always the current month you want to test? i.e. is it July because the current date (returned by TODAY()) is in July?
2. Since a range of dates can be selected on the slicer, is it sufficient if the selected date range overlaps July/current month?

EDIT:

If the answers to the above are yes, then you could write something like this:

Dynamic header =
VAR _message = "Message"
VAR ID_Intersect =
    NOT ISEMPTY ( INTERSECT ( VALUES ( 'Table 1'[ID] ), VALUES ( 'Table 2'[ID] ) ) )
VAR DateFilter_CurrentMonth =
    NOT ISEMPTY (
        INTERSECT (
            VALUES ( 'Date'[Date] ),
            PARALLELPERIOD ( TREATAS ( { TODAY () }, 'Date'[Date] ), 0, MONTH )
        )
    )
RETURN
    IF ( ID_Intersect && DateFilter_CurrentMonth, _message, " " )

Regards 🙂

[Rishabh-Maini]

Hi @OwenAuger 

This is exactly what I was looking for, thank you so much for your help!! 

The idea of using INTERSECT never occurred to me. Thank you once again! 😄

[OwenAuger · Accepted solution]

Hi @Rishabh-Maini 

I couldn't download your sample file - could you check the sharing settings?

In any case, if I've understood you correctly, the condition for displaying the message is that there is a nonempty intersection between the values of 'Table 1'[ID] and 'Table 2'[ID].

You could write a measure like this:

Dynamic header =
VAR _message = "Message"
RETURN
    IF (
        NOT ISEMPTY (
            INTERSECT (
                VALUES ( 'Table 1'[ID] ),
                VALUES ( 'Table 2'[ID] )
            )
        ),
        _message,
        " "
    )

Does this work for you?

Regards