Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
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
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!
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?
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:
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 🙂
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! 😄
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
123 | |
109 | |
88 | |
68 | |
67 |