cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gjan1982 Frequent Visitor
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):

Capture.PNG

 

 

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
gjan1982 Frequent Visitor
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.

 

 

View solution in original post

3 REPLIES 3
Community Support Team
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.

 

4.png

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERbZw1k8C6FNlflfEI...

 

Best Regards,

Amy

 

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

Highlighted
gjan1982 Frequent Visitor
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.

 

gjan1982 Frequent Visitor
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.

 

 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 116 members 1,465 guests
Please welcome our newest community members: