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
JWE
Helper I
Helper I

results of a measure use in a slicer

Hi folks

 

I need help in using results from a measure as a slicer.

 

Simple example, when the sales amount is higher than the year before the customer is a winner, else a loser.

 

I calculate the sales amount for year and last year (because I use yeartodate-function and in the report slicer for quarter and month).

I also calculate the sales difference with:

YTD U diff = salestable[YTD sales Y] - salestable[YTD sales PY]

 

Now I have a measure which split the customer into winner, neutral and loser.

Winner-Loser = IF( salestable[YTD U diff] < 0; "Loser"; IF( salestable[YTD U diff] = 0; "Neutral"; "Winner" )

    - ( When I use this as a calculated column the results always show me the total of the year, that´s wrong for my case - or can I  filter this??? )

 

To use Winner and Loser as a slicer I created an additional table 'G&V' with a column [G&V] with text rows "Winner"; "Neutral" and "Loser". (also a column 'G&V'[Nr] with integer, 1 for "Winner", 0 for "Neutral" and 2 for "Loser".)

This column I will use as a slicer. My problem is how to "connect" the results of the Winner-Loser-Measure with the column [G&V].

 

I tried measures for winner and loser like…
winner =

IF( ISFILTERED( 'G&V'[G&V] ) ; 'Tabelle1'[YTD U diff] > 0 ; Tabelle1[YTD U diff])  

 

Find an example for average (wrong for me) and try to modify, but also not the correct result.

Winner =

IF (

   ISFILTERED ( 'G&V'[Nr] );

   AVERAGEX(

       FILTER ( 'Tabelle1'; 'Tabelle1'[YTD U diff] > VALUES ( 'G&V'[Nr] ) );

       'salestable'[YTD U diff]

   ))

 

I think I have to use VALUES but I have no idea how !?

 

For any helpful hints thanks in advance

Jorg

 

actual result:

without slicer.jpg

 

and when I use the slicer, I have not the expected result - I only want to see the winner.

with slicer.jpg

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
Employee

@JWE,

 

Please create a measure below. 
Measure =
var selectedvalue = IF(HASONEFILTER(Slicer[Type]),LASTNONBLANK(Slicer[Type],0),BLANK())
return IF(HASONEFILTER(Slicer[Type]),IF(ISERROR(SEARCH(salestable[Winner-Loser],selectedvalue)),0,1),1)

And use this measure in your visual as a filter.

Capture.PNGCapture1.PNGCapture2.PNG

 

Regards,

Charlie Liao

View solution in original post

2 REPLIES 2
v-caliao-msft
Employee
Employee

@JWE,

 

Please create a measure below. 
Measure =
var selectedvalue = IF(HASONEFILTER(Slicer[Type]),LASTNONBLANK(Slicer[Type],0),BLANK())
return IF(HASONEFILTER(Slicer[Type]),IF(ISERROR(SEARCH(salestable[Winner-Loser],selectedvalue)),0,1),1)

And use this measure in your visual as a filter.

Capture.PNGCapture1.PNGCapture2.PNG

 

Regards,

Charlie Liao

Hi Charlie

 

thanks a lot. I was able to reconstruct it with your sample.

 

I hope I can combine other measures in my page. I want to use it furthermore for new and old customers, I will try it with your example.

 

Follow on question:

-- How can I COUNT the results of this measure, means when I have one more "winner", I want to use a pie chart for visuals the numbers of winner/loser/neutral.

 

Thanks for more help.

 

Cheers Jorg

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.