Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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.

 

 

v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

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/ERbZw1k8C6FNlflfEI0s71IBZSxSR30IDpHTTUaluHJbTA?e=hRo7PH

 

Best Regards,

Amy

 

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

Anonymous
Not applicable

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.