List of Values based on Selected Value

[Anonymous]

Hi All,

Could you please help me to solve this scenario.

I have attached required data in excel.

https://docs.google.com/spreadsheets/d/10Ok2ZYakQOD85XsVXDkHKok4kgbyxZ1M/edit?usp=sharing&ouid=11458...

Thank you.

[v-cazheng-msft]

Hi @Anonymous 

Not sure about your calculation logic so we couldn’t provide a formula or sample file to you, but we do provide drill down and drill through to display focus data. Here are the related documents you may refer to:

Set up drillthrough in Power BI reports - Power BI | Microsoft Docs

Drill down and drill up in a visual - Power BI | Microsoft Docs

Set up drillthrough in Power BI reports - Power BI | Microsoft Docs

Best Regards,

Community Support Team _ Caiyun

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[technolog · Accepted solution]

Create a measure:

Items for selected Group =

VAR selectedGroup = SELECTEDVALUE(Data[Group])

VAR itemsTable =
CALCULATETABLE(
VALUES(Data[Item]),
TREATAS({selectedGroup}, Data[Group])
)

RETURN
IF(
ISBLANK(selectedGroup),
"Select a group",
CONCATENATEX(itemsTable, Data[Item], ", ")
)

Add this measure into a Card visual or Table visual.

If your slicer already filters the table through a relationship, you can simplify it:

Items for selected Group =

IF(
HASONEVALUE(Data[Group]),
CONCATENATEX(VALUES(Data[Item]), Data[Item], ", "),
"Select one group"
)

If you need to limit the number of displayed items, use this version:

Items for selected Group =

VAR selectedGroup = SELECTEDVALUE(Data[Group])
VAR allItems = CALCULATETABLE(VALUES(Data[Item]), TREATAS({selectedGroup}, Data[Group]))
VAR topItems = TOPN(50, allItems, Data[Item])
VAR total = COUNTROWS(allItems)

RETURN
IF(
ISBLANK(selectedGroup),
"Select a group",
IF(
total > 50,
CONCATENATEX(topItems, Data[Item], ", ") & " … +" & FORMAT(total - 50, "#,0") & " more",
CONCATENATEX(allItems, Data[Item], ", ")
)
)