Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Rishabh-Maini
Helper II
Helper II

Compare multiple values selected in a slicer to an existing column

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

RishabhMaini_1-1689960719432.png


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

RishabhMaini_2-1689960811693.png

 

 

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!

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
Rishabh-Maini
Helper II
Helper II

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!

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?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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! 





 

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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Rishabh-Maini
Helper II
Helper II

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
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.