Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic slicing if string/column contains user selection

I need the ability to dynamically slice a matrix by whatever financial quarter the user selects. The issue is that our cases are logged on salesforce and a 'related quarter' is selected from a multi-picklist as these cases can often relate to multiple quarters (if the customer has a payment plan over 1 year there will be 4 quarters like:  Dec 20;March 20;June 20;Sept 20....not ideal for reporting)

 

To overcome this I have created a separate table with a distinct list of all the possible 'related quarter' choices. I then have a measure that searches the column of quarters and applies a value of "Show" if the value the user selected is contained. I've then put a visual filter on my table so we only see rows tagged as "Show"

You can see a similar problem here: https://community.powerbi.com/t5/Desktop/Dynamic-filtering-slicing-if-text-contains/m-p/425504

My dax (below) currently works for a normal table but NOT for a matrix (or any visual)

 

 

Dropdown = 
var mycount = 
    COUNTROWS(
        FILTER(
            VALUES('Related Quarter Filter'[Related Quarter]),
            SEARCH([Related Quarter], SELECTEDVALUE('_Customer Approvals (CAM Case)'[Related Quarter]),,BLANK())
        )
    )
return
    if(mycount > 0, "Show")

 

 

 

Is is possible to get a similar solution for matrices (this is a strong requirement, the matrix visual must be used)

 

I've attached some dummy data so you can understand how the data is structured.

 

Related QuarterCase NumberRegionFinancial Support Type
Dec 201New YorkPayment Plan
Dec 20;March 20;June 20;Sept 202BostonRental Holiday
June 203ChicagoConcession
Sept 20;March 204New YorkNot Accepted
March 205BostonPayment Plan
June 20;Dec 206ChicagoRental Holiday
March 207New YorkConcession
March 208BostonNot Accepted
March 209ChicagoPayment Plan
June 2010New YorkRental Holiday
June 2011BostonConcession
June 2012ChicagoNot Accepted
Dec 2013New YorkPayment Plan
Dec 2014BostonRental Holiday
Dec 2015ChicagoConcession
Sept 2016New YorkNot Accepted
Sept 2017BostonPayment Plan
Dec 20;March 20;June 2018ChicagoRental Holiday
Dec 20;March 2019New YorkConcession
Sept 20;June 2020BostonNot Accepted

 

Cheers

2 Replies

  • Anonymous can you share pbix file, remove sensitive information before sharing it. Share thru one drive/google drive.

     

    Also using matrix visual, what would you like on rows, columns and value?

     

    Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

    I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.