Forum Discussion
Linking Vertical List Selections
Hello All,
I apologize if this is a fairly basic question, but I cannot seem to find an answer.
Background;
Data is pulled from a Dynamics 365 Business Central server.
The only values that I'm concerned about in this example are under the table "accountNumber".
I have a slicer visual in the vertical list style where a selection for "accountNumber" can be made.
This is a combined list that includes part numbers specific to two different physical business locations.
I would like to link values together in this list, so that if one is chosen, the corresponding value for the other location is also selected.
For example, if the user selects #4460, I want it to also select #4460C. Preferably they could also be linked together to just a single entry for selecting, so the user wouldn't even know that they're technically selecting two entries.
But then in a corresponding graph, I need both selections (#4460 and #4460C) displayed in separate columns of a bar graph.
I may be overcomplicating this, but any help would be appreciated.
Thank you!
Hi Urrrshurrr ,
Based on your description, you want to also display #4460C in the bar graph when the user selects #4460. We can recognize #4460 as a parent category, create a custom table and measures, and determine if the #4460C in the current row contains the selected parent category. DAX reference: CONTAINSSTRINGEXACT function (DAX) - DAX | Microsoft Learn
Step1. Custom table.
test = VALUES('Table'[accountNumber])Step2. Measures.
SelectedAccNum = VAR _selected = ALLSELECTED('Table'[accountNumber]) RETURN IF(MAX('test'[accountNumber]) IN _selected || CONTAINSSTRINGEXACT(MAX('test'[accountNumber]),SELECTEDVALUE('Table'[accountNumber])),1,0)Separate measure need to be created for bar graph value display.
SumValue = CALCULATE(SUM('Table'[Value]),ALL('Table'),'Table'[accountNumber]=MAX('test'[accountNumber]))Step3.Apply Filter to control the bar chart column display.
The output.
Best regards,
Mengmeng Li
5 Replies
Another option, if your list of values is not too long, is to create a custom group in the model: Right-click on the field in quesiton, and select New Group at the bottom. Grab all the 4460 members and create a group of them. Do likewise for others.
The disadvantage of this solution is that it is not practical if you have a lot of distinct values to manage, or if you are getting more values regularly. Also, the groups only reside in this model and are not shared with other models.
What can you tell us about your data? Is that number ALWAYS 4 digits and then MAYBE a suffix letter? If so, then create a calculated column (DAX or Power Query) that is the left 4 character, and filter on that.
If that won't work, can you give us extended sample data?
Hi Urrrshurrr ,
Based on your description, you want to also display #4460C in the bar graph when the user selects #4460. We can recognize #4460 as a parent category, create a custom table and measures, and determine if the #4460C in the current row contains the selected parent category. DAX reference: CONTAINSSTRINGEXACT function (DAX) - DAX | Microsoft Learn
Step1. Custom table.
test = VALUES('Table'[accountNumber])Step2. Measures.
SelectedAccNum = VAR _selected = ALLSELECTED('Table'[accountNumber]) RETURN IF(MAX('test'[accountNumber]) IN _selected || CONTAINSSTRINGEXACT(MAX('test'[accountNumber]),SELECTEDVALUE('Table'[accountNumber])),1,0)Separate measure need to be created for bar graph value display.
SumValue = CALCULATE(SUM('Table'[Value]),ALL('Table'),'Table'[accountNumber]=MAX('test'[accountNumber]))Step3.Apply Filter to control the bar chart column display.
The output.
Best regards,
Mengmeng Li
Thank you ToddChitt and Anonymous !
The options suggested by both of you make sense and I'm sure one of them will work for my situation.
I will do some testing and report back with which option ended up being the best for me!
Thanks again.
Hi Urrrshurrr ,
How is the situation now? If the problem has been solved, please accept the replies you find helpful as solutions.
Best regards,
Mengmeng Li
