Forum Discussion

PBI_AMS's avatar
PBI_AMS
Helper I
1 year ago
Solved

Slicing a visual while excluding the selected value from the visual

I am analyzing subscriber data.  I have 100k plus subscribers who have opted into up to 10 topics. What I want to be able to see is: If you are a subscriber to topic C, what OTHER topics are you also subscribed to?  To do this, I have created a contact filter table and contact output table with many-to-many connection on contact GUID.  If I select topic C in a slicer from the filter table, I can see a bar chart of topics A-J using data from the output table.  This visual shows topic C as the highest number of subscribers but also shows what other topics those subscribers are interested in.  This provides some insights from multi-select as well. X # of subscribers to C AND/OR F will also be interested in J.  However, the behavior I want is to EXCLUDE any items I've selected in my Slicer from the output visual. My searches have yielded several (nifty!) tutorials for how to create a slicer to exclude selected items from a visual but not while simultaneously slicing that visual by those selections.  Any help?  

Need Help
  • v-sshirivolu's avatar
    v-sshirivolu
    1 year ago

    Hi PBI_AMS ,

    Thanks for reaching out to the Microsoft fabric community forum.

     

    Try this DAX measure :

     

    ExcludedTopics =
    VAR SelectedTopics =
        VALUES(ContactFilterTable[SelectedTopic])

    VAR HasSelection =
        COUNTROWS(SelectedTopics) > 0

    VAR SelectedContacts =
        CALCULATETABLE(
            VALUES(ContactOutputTable[ContactGUID]),
            ContactOutputTable[Topic] IN SelectedTopics
        )
    RETURN
    IF(
        HasSelection,
        CALCULATE(
            COUNTROWS(ContactOutputTable),
            FILTER(
                ContactOutputTable,
                ContactOutputTable[ContactGUID] IN SelectedContacts &&
                NOT(ContactOutputTable[Topic] IN SelectedTopics)
            )
        ),
        0
    )


    Find the below Attached .pbix file for your reference.

    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

    Best Regards,
    Sreeteja.
    Community Support Team 





5 Replies

  • PBI_AMS's avatar
    PBI_AMS
    Helper I
    1 year ago

    Okay, maybe I just had to type that out to get to my answer.  I created thie following measure to use in the visuals, and it works like a charm! 

    ExcludedTopics = CALCULATE( COUNTROWS(ContactOutputTable), FILTER( ContactOutputTable, NOT(ContactOutputTable[Topic] IN VALUES(ContactFilterTable[SelectedTopic])) ) )

    • v-sshirivolu's avatar
      v-sshirivolu
      Community Support
      1 year ago

      Hi PBI_AMS ,

      Thanks for reaching out to the Microsoft fabric community forum.

       

      Try this DAX measure :

       

      ExcludedTopics =
      VAR SelectedTopics =
          VALUES(ContactFilterTable[SelectedTopic])

      VAR HasSelection =
          COUNTROWS(SelectedTopics) > 0

      VAR SelectedContacts =
          CALCULATETABLE(
              VALUES(ContactOutputTable[ContactGUID]),
              ContactOutputTable[Topic] IN SelectedTopics
          )
      RETURN
      IF(
          HasSelection,
          CALCULATE(
              COUNTROWS(ContactOutputTable),
              FILTER(
                  ContactOutputTable,
                  ContactOutputTable[ContactGUID] IN SelectedContacts &&
                  NOT(ContactOutputTable[Topic] IN SelectedTopics)
              )
          ),
          0
      )


      Find the below Attached .pbix file for your reference.

      If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

      Best Regards,
      Sreeteja.
      Community Support Team 





      • v-sshirivolu's avatar
        v-sshirivolu
        Community Support
        1 year ago

        Hi PBI_AMS  ,
        May I ask if you have resolved this issue? If so, please mark the helpful reply. This will be helpful for other community members who have similar problems to solve it faster.