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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors