Forum Discussion
Issue With Relationship / DAX Not Showing Correct Data
Hi all,
I’m facing an issue and I’d really appreciate any suggestions or sample approaches.
Data Sources and Tables
-
Topics → from SharePoint Excel (via Dataflow)
-
Messages → from Azure (Shortcut, DirectQuery)
-
Messages_1 → a second copy of Messages to avoid filter loops
Relationships
-
Topics to Messages → active relationship (crossfilter = Both) (1 To Many)
-
Topics to Messages_1 → no active relationship (to prevent conflicts)
So effectively:
-
Messages is driving the slicer/filter.
-
Messages_1 is used for mapping topic_label to topic_group without disturbing filters.
Sample Data
Messages / Messages_1
| conversation_id | topic_label |
|---|---|
| 101 | Billing and Payment Issues |
| 102 | Customer Support Live Chat |
| 103 | Outlier Cluster |
| 104 | Online Invoice Payment Process |
| 105 | Account Address Update |
| 106 | Greetings and Salutations |
Topics
| topic_id | topic_label | topic_group |
|---|---|---|
| -1 | Outlier Cluster | Outlier Cluster |
| 1 | Billing and Payment Issues | Billing and Payment Management |
| 2 | Customer Support Live Chat | Customer Support and Communication |
| 3 | Online Invoice Payment Process | Invoice Management and Payments |
| 4 | Account Address Update | Account Management |
| 5 | Greetings and Salutations | Polite Expressions |
Goal
When a conversation_id is selected from Messages, I want to show:
-
topic_label from Messages
-
the correct topic_group from Topics (via Messages_1)
Measure Tried
Topic Group =
CALCULATE (
SELECTEDVALUE ( Topics[topic_group] ),
TREATAS ( VALUES ( Messages_1[topic_label] ), Topics[topic_label] )
)Problem
-
Works only for "Outlier Cluster".
-
For other labels (like "Billing and Payment Issues" or "Customer Support Live Chat"), the measure returns blank, even though the text values exist in both tables.
Debugging with:
Label Debug =
"Stream [" & SELECTEDVALUE ( Messages_1[topic_label] ) & "] | Topic [" & SELECTEDVALUE ( Topics[topic_label] ) & "]"… shows that only some values align. Others show nothing.
Could anyone please suggest / explain what is the issue and provide a solution to the issue maybe a sample .pbix file.
Any guidance or even a rough example would be really helpful.
Thanks in advance!
Hi Akash_Varuna ,
Thank you for reaching out to the Microsoft fabric community forum. Also thank you wardy912 for you helful response.
The behavior you observed is due to the way SELECTEDVALUE works; it only returns a single value when there is one value in the current filter context. When a conversation_id is associated with multiple topic_labels, the function will either return one value or blank, which is why you were not getting the expected results.
To address this, you can use CONCATENATEX instead, which will return all topic groups linked to the selected conversation, separated by commas:
Topic Groups = CALCULATE ( CONCATENATEX ( VALUES ( Topics[topic_group] ), Topics[topic_group], ", " ), TREATAS ( VALUES ( Messages_1[topic_label] ), Topics[topic_label] ) )Please find the attached .pbix and test it in your environment. You should now see multiple topic groups correctly displayed whenever a conversation_id maps to more than one label.
Hope this helps, please feel free to rech out for any further questions.
Thank you.
7 Replies
Hi Akash_Varuna
I've replicated your method with your dummy data and relationship structure, unfortunately i'm not seeing an error. Unless i've misunderstood, it's working as expected.
Topic Group = CALCULATE( SELECTEDVALUE(Topics[topic_group]), TREATAS( VALUES(Messages_1[topic_label]), Topics[topic_label] ) )Messages is driving the slicer:
The only difference with mine is it's not direct query, could that be causing an issue?
Hi wardy912 Thanks for checking, appreciate it.
The issue in my model is that a singleconversation_idcan have multipletopic_labelsunder it.
For example, the same conversation may contain both Outlier Cluster and Billing and Payment Issues. In that case, the current DAX only shows the Outlier Cluster group and blanks out the rest.
I need a way to correctly return all topic groups for the conversation.Very strange, i've added multiple topic_labels and I still have no issue with the above configuration.
Hi Akash_Varuna ,
Thank you for reaching out to the Microsoft fabric community forum. Also thank you wardy912 for you helful response.
The behavior you observed is due to the way SELECTEDVALUE works; it only returns a single value when there is one value in the current filter context. When a conversation_id is associated with multiple topic_labels, the function will either return one value or blank, which is why you were not getting the expected results.
To address this, you can use CONCATENATEX instead, which will return all topic groups linked to the selected conversation, separated by commas:
Topic Groups = CALCULATE ( CONCATENATEX ( VALUES ( Topics[topic_group] ), Topics[topic_group], ", " ), TREATAS ( VALUES ( Messages_1[topic_label] ), Topics[topic_label] ) )Please find the attached .pbix and test it in your environment. You should now see multiple topic groups correctly displayed whenever a conversation_id maps to more than one label.
Hope this helps, please feel free to rech out for any further questions.
Thank you.
Hi Akash_Varuna ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Hi Akash_Varuna ,
I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.
Thank you.
Hi Akash_Varuna ,
We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.Thank you.
