Forum Discussion
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 Quarter | Case Number | Region | Financial Support Type |
| Dec 20 | 1 | New York | Payment Plan |
| Dec 20;March 20;June 20;Sept 20 | 2 | Boston | Rental Holiday |
| June 20 | 3 | Chicago | Concession |
| Sept 20;March 20 | 4 | New York | Not Accepted |
| March 20 | 5 | Boston | Payment Plan |
| June 20;Dec 20 | 6 | Chicago | Rental Holiday |
| March 20 | 7 | New York | Concession |
| March 20 | 8 | Boston | Not Accepted |
| March 20 | 9 | Chicago | Payment Plan |
| June 20 | 10 | New York | Rental Holiday |
| June 20 | 11 | Boston | Concession |
| June 20 | 12 | Chicago | Not Accepted |
| Dec 20 | 13 | New York | Payment Plan |
| Dec 20 | 14 | Boston | Rental Holiday |
| Dec 20 | 15 | Chicago | Concession |
| Sept 20 | 16 | New York | Not Accepted |
| Sept 20 | 17 | Boston | Payment Plan |
| Dec 20;March 20;June 20 | 18 | Chicago | Rental Holiday |
| Dec 20;March 20 | 19 | New York | Concession |
| Sept 20;June 20 | 20 | Boston | Not Accepted |
Cheers
- Anonymous5 years ago
I figured it out myself parry2k
The solution is in attached pbix if anyone is interested
https://drive.google.com/file/d/1EUCM2B5Mm7cZJ_mZcZoRyRsmAD60QNrU/view?usp=sharing
2 Replies
- parry2kSuper User
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.⚡
- AnonymousNot applicable
I figured it out myself parry2k
The solution is in attached pbix if anyone is interested
https://drive.google.com/file/d/1EUCM2B5Mm7cZJ_mZcZoRyRsmAD60QNrU/view?usp=sharing