Set field parameter value based on filtering of another slicer

[JozefR]

Hello,

I was wondering if it's possible to set value in Field parameter based on filtering of another slicer. My report is based on following table (example):

Department	Business_key	Plan
Admin	AD1	40
Admin	AD2	20
Admin	AD3	30
Finance	FI1	50
Finance	FI2	10
IT	IT1	30
IT	IT2	20
IT	IT3	40
IT	IT4	80
IT	IT5	60

I created a new filed parameter which contains two fields: Department and Business_key and I also added new slicer based od this parameter to the page. Then I created a new pie chart which contains Parameter as Legend and Plan as Value.

I also added a new slicer based on the Department field  - I need this slicer for filtering some other visuals on this page. Everything works fine so far and my report looks like this:

My scenario is: IF(ISFILTERED('Hárok1'[Department]; Parameter[Parameter] = Business_key; Parameter[Parameter] = Department)

I realize of course that the formula above is wrong but I would like to say: if Department slicer is filtered, show me values filtering by Business Key, otherwise by Department in pie chart.

Thanks.

[HassanAshas]

Has anybody found a solution to this problem? 

[JozefR]

Hi Jianbo Li,

thanks for the reply but it doesn't match my scenario. You replaced my Department slicer with new "Value" slicer changing based on Attribute slicer , but as i mentioned above, I need Department slicer unchanged with only departments values becasue this slicer is used to filter other visuals on this page (I didn't show them in my example).

Regards

JozefR

[v-jianboli-msft]

Hi @JozefR ,

You can achieve your goal by using Unpivot in Power Query.

Please try:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckzJzcxT0lFydDEEkiYGSrE6yIJGQNIIXdAYSBpDBN0y8xLzklOBAm6eIANMMYVBRhhChD1DgGzPEEOEfqgIkjVQEWOEa6AiJkDSAkXEFEiaAUViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Department = _t, Business_key = _t, Plan = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Department", type text}, {"Business_key", type text}, {"Plan", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Plan"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Then create visuals:

Create a slicer for Attribute:

Turn on the single select option:

Then create a slicer for value:

Create a Pie chart:

Final output:

Best Regards, 

Jianbo Li 

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