Forum Discussion
Returning multiple slicer values based off one slicer selection
In power bi I have a table with two columns called AltOrgCode which is a list of codes, and a column called OrgAbbreviated which is a list of organizations, AltOrgCodes only contains distinct values whereby OrgAbbreviated can have the same organizations more than once but with a different AltOrgCode assigned. I need to be able to select one AltOrgCode in a slicer and have it return all AltOrgCodes relating to that OrgAbbreviated value? How do I do this?
For example below. If I selected AltOrgCode EGS from my slicer I would need all data in a table to show for EGS, EGSA and EGSB.
Using the OrgAbbreviated field in the slicer was proposed but the stakeholder specifically wants the AltOrgCode to be the slicer options.
Any help would be greatly appreciated! Thank you in advance đ
Hi Anonymous , Thank you for reaching out to the Microsoft Community Forum.
Create a mapping table that captures the relationship between AltOrgCodes and OrgAbbreviated. Example:
AltOrgMapping =
SUMMARIZE(
Sheet1,
Sheet1[AltOrgCodes],
Sheet1[OrgAbbreviated]
)
Next, create a table disconnected from your data model for the AltOrgCodes. Example:
AltOrgCodeSlicer =
DISTINCT(AltOrgMapping[AltOrgCodes])
Then, create a measure to determine whether each row in your data matches the OrgAbbreviated tied to the selected AltOrgCode. Example:
ShowRelatedAltOrgCodes =
VAR SelectedCode = SELECTEDVALUE(AltOrgCodeSlicer[AltOrgCodes])
VAR SelectedOrg =
CALCULATE(
MAX(AltOrgMapping[OrgAbbreviated]),
AltOrgMapping[AltOrgCodes] = SelectedCode
)
RETURN
IF(
MAX(Sheet1[OrgAbbreviated]) = SelectedOrg,
1,
0
)
Use the AltOrgCodeSlicer[AltOrgCodes] column in your slicer and apply the ShowRelatedAltOrgCodes measure as a visual-level filter set to 1. This will ensure that when one AltOrgCode is selected, all rows with the same OrgAbbreviated and their corresponding AltOrgCodes are shown.
If this helped solve the issue, please consider marking it âAccept as Solutionâ and giving a âKudosâ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
6 Replies
Create a new calculated column
RelatedAltOrgCodes =
CALCULATE(
CONCATENATEX(
FILTER(
TableName,
TableName[OrgAbbreviated] =
CALCULATE(
VALUES(TableName[OrgAbbreviated]),
TableName[AltOrgCodes] = SELECTEDVALUE(TableName[AltOrgCodes])
)
),
TableName[AltOrgCodes],
", "
)
)
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
Hi Anonymous , Thank you for reaching out to the Microsoft Community Forum.
Create a mapping table that captures the relationship between AltOrgCodes and OrgAbbreviated. Example:
AltOrgMapping =
SUMMARIZE(
Sheet1,
Sheet1[AltOrgCodes],
Sheet1[OrgAbbreviated]
)
Next, create a table disconnected from your data model for the AltOrgCodes. Example:
AltOrgCodeSlicer =
DISTINCT(AltOrgMapping[AltOrgCodes])
Then, create a measure to determine whether each row in your data matches the OrgAbbreviated tied to the selected AltOrgCode. Example:
ShowRelatedAltOrgCodes =
VAR SelectedCode = SELECTEDVALUE(AltOrgCodeSlicer[AltOrgCodes])
VAR SelectedOrg =
CALCULATE(
MAX(AltOrgMapping[OrgAbbreviated]),
AltOrgMapping[AltOrgCodes] = SelectedCode
)
RETURN
IF(
MAX(Sheet1[OrgAbbreviated]) = SelectedOrg,
1,
0
)
Use the AltOrgCodeSlicer[AltOrgCodes] column in your slicer and apply the ShowRelatedAltOrgCodes measure as a visual-level filter set to 1. This will ensure that when one AltOrgCode is selected, all rows with the same OrgAbbreviated and their corresponding AltOrgCodes are shown.
If this helped solve the issue, please consider marking it âAccept as Solutionâ and giving a âKudosâ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Hi Anonymous ,
I wanted to follow up and see if youâve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!Hi Anonymous , Just checking inâwere you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!Hi Anonymous ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!
