cancel
Showing results for
Did you mean:
Frequent Visitor

## Creating dynamic measures using a slicer to change matrix values

Hi,

I have a matrix with portfolios AAA, BBB and CCC running along the top and currencies EUR, GBP, USD running down the side. There are two columns per portfolio, (1) Portfolio% and (2) Diff to Benchmark% (see highlighted yellow).

Diff to Benchmark% is a measure that is calculated by subtracting the Benchmark from each Portfolio. In the below exmaple, Benchmark = Portfolio AAA.

E.g. Diff to Benchmark% for portfolio BBB, on the EUR row (cell E3) = 10% - 5% = 5%.

I have been able to create a working measure by hard coding "AAA" as the benchmark - see DAX code extract below. However I would like to create a slicer that dymanically changes the Benchmark portfolio from a list. I have attempted to do this by amending the red line of code to equal a measure from a benchmark slicer but I get the following error message:

"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Any help would be greatly appreciated.

Desired Result - Example 1 (Benchmark slicer = AAA):

Existing DAX Measure (hard coded benchmark):

Diff to Benchmark =
VAR _portfolio = [portfolio%]
VAR _benchmark = CALCULATE(
[portfolio%],
'table'[PortfolioID] = "AAA",
all('table'[SortPortfolio])
)
RETURN
_portfolio - _benchmark

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Creating dynamic measures using a slicer to change matrix values

I thought I would loop back and close this off with my solution to the problem.

In order to make the Diff to Benchmark measure work, we first need to create another measure called Benchmark Portfolio (see code below) that relates to a single selection (using a dropdown slicer) based on a table called 'Unique Portfolios'. 'Unique Portfolios' is a standalone table that holds all unique portfolios and has no relationship to the rest of the data model.

Benchmark Portfolio = if(HASONEVALUE('Unique Portfolios'[Portfolio]),values('Unique Portfolios'[Portfolio]),0)

We then take this measure and add it as a variable in the Diff to Benchmark measure (in red).

Diff to Benchmark =
VAR _portfolio = [portfolio%]
VAR _benchport = [Benchmark Portfolio]
VAR _benchmark = CALCULATE(
[portfolio%],
'table'[PortfolioID] = _benchmarkport,
all('table'[SortPortfolio])
)
RETURN
_portfolio - _benchmark

This gives us our desired result of subtracting one cell (the benchmark portfolio) from another cell (the portfolio) in a matrix table based on a benchmark portfolio selected in a slicer.

3 REPLIES 3
Highlighted
Community Support Team

## Re: Creating dynamic measures using a slicer to change matrix values

Hi @gjan1982 ,

According to tour description, I create sample date to test your scenario. The [Portfolio%] is a measure but not a column, right? In my test, assuming [Portfolio%] = AVERAGE('table'[Portfolio])/1. following the steps below, it will return your desired output.

1.Create a calculated table, and let [Benchmark slicer] as the source of slicer.

Selector = SELECTCOLUMNS('table',"Benchmark",'table'[PortfolioID],"Benchmark percent",'table'[Portfolio%])

2.Then create measure like DAX below.

Diff to Benchmark =

VAR selected =SELECTEDVALUE(Selector[Benchmark percent])

RETURN

[Portfolio%]-selected

3.Result.

Best Regards,

Amy

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

Frequent Visitor

## Re: Creating dynamic measures using a slicer to change matrix values

Hi Amy,

Thanks for the advice. Unfortunately your test pbix only works when I select portfolio AAA. Other selections do not appear to have any effect.

Frequent Visitor

## Re: Creating dynamic measures using a slicer to change matrix values

I thought I would loop back and close this off with my solution to the problem.

In order to make the Diff to Benchmark measure work, we first need to create another measure called Benchmark Portfolio (see code below) that relates to a single selection (using a dropdown slicer) based on a table called 'Unique Portfolios'. 'Unique Portfolios' is a standalone table that holds all unique portfolios and has no relationship to the rest of the data model.

Benchmark Portfolio = if(HASONEVALUE('Unique Portfolios'[Portfolio]),values('Unique Portfolios'[Portfolio]),0)

We then take this measure and add it as a variable in the Diff to Benchmark measure (in red).

Diff to Benchmark =
VAR _portfolio = [portfolio%]
VAR _benchport = [Benchmark Portfolio]
VAR _benchmark = CALCULATE(
[portfolio%],
'table'[PortfolioID] = _benchmarkport,
all('table'[SortPortfolio])
)
RETURN
_portfolio - _benchmark

This gives us our desired result of subtracting one cell (the benchmark portfolio) from another cell (the portfolio) in a matrix table based on a benchmark portfolio selected in a slicer.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 232 members 2,633 guests
Recent signins: