Filter a Matrix Visual When one value is never empty

[Kgraham01]

Hello all, 

Needing a bit of help for as I am learning power BI. I am using power BI to report out of our Dynamics 365 sales CRM. 

I have a matrix that is listing all the opportunities showing their estimate revenue and a count of their order lines. I have also include a link to each opportunity. This is where I am running into issues. 

When a different visual in the report is clicked, I want it to filter and only show the oppty related to that filter. But because every oppty has a link regardless of the filter. They don't hide as expected. Is there a way I can get the filter to ignore that column, or some other setting I can try? 

Thanks! 

[Shravan133 · Accepted solution]

To achieve your goal of filtering opportunities while maintaining hyperlinks, you can use measures and conditional formatting. Here's a step-by-step guide to help you resolve the issue:

1. Create a Measure for the Hyperlink:

   • Instead of directly adding a hyperlink column, create a measure that generates the hyperlink dynamically based on the filtered data.

   • Use the following DAX formula as an example:

     DAX

     Copy code

     OpportunityLink = IF( HASONEVALUE(Opportunities[OpportunityID]), "https://yourcrm.url/opportunity/" & VALUES(Opportunities[OpportunityID]), BLANK() )

     This measure ensures that the link is only generated when a single opportunity is selected.

2. Add the Measure to the Matrix:

   • Add the OpportunityLink measure to your matrix visual instead of the static hyperlink column.
   • This way, the hyperlink will only appear when the data is filtered to a specific opportunity.

3. Adjust Visual Interactions:

   • Ensure that the interactions between visuals are set correctly.
   • Go to the "Format" pane, select "Edit interactions," and make sure the visuals interact with each other as intended. Verify that the matrix visual is set to be filtered by the other visuals.

4. Conditional Formatting for Hyperlinks:

   • Use conditional formatting to apply hyperlinks dynamically.
   • In your matrix visual, go to the field where you want the hyperlink to appear.
   • Click on the dropdown arrow next to the field name and select "Conditional formatting."
   • Choose "Web URL" and then set it to use the OpportunityLink measure you created

[Kgraham01]

Thank you so much! that worked for me and got the result I was looking for.