SUMMARIZE - Switch Columns using a Parameter/Filter

[Anonymous]

Hi,

I've added a slicer to my report from a measure, based on the following link:

https://community.powerbi.com/t5/Desktop/a-way-to-use-a-measure-for-a-slicer/td-p/2086558

It's all working fine but I want to add something a bit more dynamic to it. At the moment, he's the calculation I'm working with:

Spend Slicer Filter = 
VAR Virtual = 
SUMMARIZE(
'Spend & TARPs (Nielsen)',
    'Spend & TARPs (Nielsen)'[Advertiser],
    'Spend & TARPs (Nielsen)'[Media Type],
    "Spend", [Spend (Nielsen)])
VAR MinValue = MINX(Virtual,[Spend])
VAR MaxValue = MAXX(Virtual,[Spend])
VAR CurrentSlicerValue = SELECTEDVALUE('Spend Filter'[Spend Filter])
RETURN
IF(CurrentSlicerValue >= MinValue && CurrentSlicerValue <= MaxValue, 1, 0)

This allows me to create a table by Advertiser and Media Type, with a column for Spend coming from an existing measure. This measure is used to identify Spend between a certain value, which allows me to filter by a measure. In this instance it looks at the total Spend by Advertisers and Media Type. 

But in my report I'm using a number of parameters to switch axis from Advertiser to Product, and to switch Media Type to Media Sub Type. I've like to be able to do the same within this calculation, but I'm not sure how to write it.

As a result, the total Spend for a Product and Media Sub Type can be smaller than that for an Advertiser and Media Type. I therefore want my slicer to show relevant values when these parameters are changed.

I've used the parameter function from Modeling to create my parameters, so they aren't on the same table. I tried to add in a simple IF statement, but that didn't work.

Does anyone have any ideas on how I can achieve this?

Thanks,

Mark

[v-zhangti]

Hi, @Anonymous 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Hi @v-zhangti ,

I was going to upload a PBIX file or Excel file but I can't see how to do that. So hopefully these images will help.

In the below images you'll see some filters with purple boarders and some parameters with blue borders. The default setting at this stage is Advertiser and Media Type, as per the original DAX:

With this setting, the Spend Filter shows a MAX value, generated from the original DAX statement, that matches the table below.

However, if I amend these parameters to show Media Sub Type, the MAX value doesn't adjust:

I would like this to adjust based on the parameter selection. Which is where I was wondering if we could swap out Advertiser for Product and Media Type for Media Sub Type in the DAX statement, so this is more dynamic.

Does that help?

Thanks,

Mark