Show data in the visuals based on the aggregation level selected by user in a portal

[anandmitta]

I have a Power Bi report with only a Table Visual. I embed the report to a portal.

In the portal, we have dropdown with different levels of aggregation as 

1) Name

2) Name and Age

3) Name, Age and DOB

I want to display just the data that the user selects from the dropdown in the table visual in my report .

Example:

When the user selects "Name" from the dropdown in the portal, I have to just display only the Name column in the table visual in my report.

When the user selects "Name and Age" from the dropdown in the portal, I have to display both the Name and Age columns in the table visual in my report.

When the user selects "Name, Age and DOB" from the dropdown in the portal, I have to display Name, Age and DOB columns in the table visual in my report.

Is there any way to achieve this using Dynamic URL filtering?

[Anonymous · Accepted solution]

Hi @anandmitta 

Power BI doesn't support you to filter the visual by the filter in your portal. But you can try to filter your visual by a slicer in Power BI Report. To show dynamic result by filter column name in slicer, Matrix visual is a good way.

Add an Index column in Sample Table in Power Query Editor.

Get a Column Name Table by M query.

let
    Source = Table.ColumnNames(#"Sample"),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Column Name"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Column Name", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column Name] <> "Index"))
in
    #"Filtered Rows"

Build a Measure.

Measure = 
VAR _SelectColumn = SELECTEDVALUE('Filter'[Column Name])
RETURN
SWITCH(_SelectColumn,"Name",MAX('Sample'[Name]),"Age",FORMAT(SUM('Sample'[Age]),0),"DOB",SUM('Sample'[DOB]))

Result by default.

Select Age and Name in Slicer.

Best Regards,

Rico Zhou

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