Dynamic Filters on Dimension and Measures

[srlabhe123]

Hi All,

I want to display the diimension and their measures based on selection in the Table Visual.

Like below Delivery Type, Brand, Manufacturer .... Materials these all are dimenssions and

SKUs, SKUs LY,SKUs YoY,Sales,Sales LY and so on are measures.

I want to have above filters where user can select whatever dimensions and measures they want to see in Table Visual.

For Ex :- If user selects Dimenssion Delivery Type and measures SKUs, SKUs YoY so only these 3 fields should display in tabel visual.

Kindly help, please note the dimensions and measures are from same query.

[parry2k]

I don;t think it is possible but here is hack to do it but will not very pretty

Here is high level how you can do it:

1. Add two tables with Dimension and Measures name in it.

2. Put these as field as slicer

3. Each of your dimension and measure will have if condition , for example "Deliver Type" dimension will be condition like 

    Delivery Type = IF(SelectedValue(TableDimension[DimensionName]) = "Delivery Type", Table[Delivery Type], BLANK()). You have to do it for every dimension/measure

4. Add table visual, drop all dimension/measures in table

Based on value selected in the slicer, you will see only selected dimension/measures values will show up in Table, and others will be blank.

Here is why it will not look pretty:

1. Header for all dimension/measures will be always visible.

2. All the columns in table will not be next to each other, so there will be gap in the table depending

Thanks,

P

[srlabhe123]

Can isue Swicth in stead of if so that I can do it in single siwtch statement like blow:

SWITCH(SelectedValue(TableDimension[DimensionName]), "Delivery Type", Table[Delivery Type],and so on

[parry2k]

unfortuantely i don';t think switch will work on this, and I also thing selectedvalue is going to work , I just gave you an idea, selectedvalue only works if user select one value in the slicer.

[srlabhe123]

Created a below column 

SelectedDim = IF(SELECTEDVALUE('Dimensions'[Dimensions])="Delivery Type",KPI_Sales[Delivery Type],"NUll") 

when I select Delivbery Type in slicer below it still does not refletc Delivery Type with its values form main table.

It seems Selected Value is not working for single value too.

Thanks

[parry2k]

please add it as measure

[parry2k]

my mistake, yes, we need aggregation

if("Delivery Option" IN VALUES(TableDimension[DimensionName]), MAX(Table[Delivery Option]), BLANK())

You can work on this proposed solution but I don';t think it is very scalable and good approach, although at the same time I don't see any other solution.

I will leave it upto you to make a decision to see if you want to continue on this path 🙂

[srlabhe123]

[parry2k]

I know, give it a try , may be it worked out.

[srlabhe123]

Trying for same... since morning 

[parry2k]

I hear ya, so are we getting somewhere or still need further help.

[srlabhe123]

we need help, am trying to serach online for same...

[parry2k]

what happened to proposed solution, did you tried it?

[srlabhe123]

I cant use aggrgated function as I need whole olumn value to display

[v-huizhn-msft]

Hi @srlabhe123,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will get useful information from here.

Thanks,
Angelia

[gnanavadivel100]

Hi Guys,

I found an interesting and useful solution by Angelia, where we can do a Dynamic Selection on Measures list.

We could achieve Dynamic Dimension Selection as well with Quick work around.
You can refer by blog....
https://wordpress.com/post/gunaseelangnanavadivel.wordpress.com/23

Happy learning!
Thanks.

Guna

[parry2k]

@gnanavadivel100link doesn't open, although I already did it recently but would like to see what are the other methods.

[gnanavadivel100]

Please try with the below

https://gunaseelangnanavadivel.wordpress.com/2018/07/14/powerbi-dynamic-view-based-on-dimension-sele...

Cheers,

Guna

[v-huizhn-msft]

Hi @srlabhe123,

Please create a table inclusing dimension value, create another measure using switch function. More details, please review this blog.

Best Regards,
Angelia

[srlabhe123]

Adding it as measure will ask me to specify aggrgation....but I want that whole coulmn to display after selection and not aggrgated value.

[parry2k]

Instead of selectedvalue you need to check following and that will take care if use selected more than one value in slicer:

if("Delivery Option" IN VALUES(TableDimension[DimensionName]), Table[Delivery Option], BLANK())