1 slicer's to multiple measures

[ThomasDay]

Hello fellow Dax'ers,

I'm looking to parse a slicer's results into multiple measures.  The challenge is as shown here.  Company Names are text.  

I am going to restrict choices to 3--and haven't put in those traps yet...

The challenge seems the same regardless of how many choices I think

I figured I'd use TOPN(1,...) to pick the first...works great!  

MetricChoice1 = Calculate (SUM(Measures[Metric Value]), Filter(Companies,Companies[Company_Name] = TopN(1,Values(Companies[Company_Names]))))

Now to pick off choice #2, I'm trying to create a list of names less the Choice1.  

MetricChoice2 = 
	VAR Choices = TOPN(3,Values(Companies[Company_Name]))
	VAR Choice1 = TOPN (1,Values(Companies[Company_Name]))
	VAR LessChoice1 =EXCEPT(Choices, Choice1)
// Was hoping this gave me a table with only the last two entries.
//So I could then do the same thing as before	
	Return	
	Calculate (SUM(Measures[Metric Value]), Filter(Companies,Companies[Company_Name] = TopN(1,LessChoice1)))

I get a frown and have to shut down....:-(

Any ideas here?  Thanks, Tom

[v-qiuyu-msft]

Hi @ThomasDay,

Would you mind sharing the sample .pbix file? So that we are able to see the issue directly.

Best Regards,
Qiuyun Yu

[ThomasDay]

It's a little big to share directly.  Perhaps you can see it with these screen shots.

This is the matrix visualization--the three indicators in blue are EACH computed from thousands of data points.  The two columns in yellow are EACH computed for ONE data point.  Not every row will have data in each column.  This is a common and routine matrix situation.  The frown happened with the comparison 2 column--which is filtered as you can see from the code--when it did not have any data for a row being displayed.  I guess the slicer changes with every row it works on...which means it's not right for Column 1 either if it doesn't have data.  See the third screen shot

Comparison 2 = 
VAR Choices = Values(HospProviders[Hospital_Name])  //This takes all the choices and puts the names in a list
VAR Choice1 = TOPN (1,Values(HospProviders[Hospital_Name]))  //This is the first choice
VAR LessChoice1 =EXCEPT(Choices, Choice1) //this is gives me all but the first choice
Return 
IF(COUNTROWS(LessChoice1)>0,
		FORMAT(Calculate(SUM(HospMeasures[Metric Value]), 
		Filter(HospProviders,HospProviders[Hospital_Name] = TopN(1,LessChoice1))),"Standard"),
		0)

In the screen shot below, I show the value of the 1st slicer element selected.  (In this example there are several slicer selections made)  The slicer values that the matrix visualization works with, however, actually change with each row.  You can see how the filter will change when choice 1 doesn't have data...and selection 2 now moves up to be the 1st selection--and so on!   Yikes!!

I hope this helps,

Tom

[sornavoor]

Create a table with the slicer values that is not connected other tables on the data model and place it in a  slicer. 

Then  use the selected slicer value  in a measure  that uses it and use the measure in your visuals. 

Please see approach http://www.daxpatterns.com/parameter-table/

[ThomasDay]

@sornavoor I have it working for the first of the values from my slicer list.  It works like a champ.  I can select a dozen facilities down further on the slicer list and the metric stays nailed down at the first selection.  That's perfect.  

What I'm trying to figure out is how to get the second value out of the list.  

Any ideas on that?  Tom

[Greg_Deckler]

VALUES function?

https://msdn.microsoft.com/en-us/library/ee634547.aspx

[ThomasDay]

Shoot I left it off the second one...let me try that.

[ThomasDay]

I guess I'm pretty tangled up:  Here goes a verbal description of each line and perhaps you can see where I'm astray.  

MetricChoice2 =
VAR Choices = Values(HospProviders[Hospital_Name]) //This takes all the choices and puts the names in a list
VAR Choice1 = TOPN (1,Values(HospProviders[Hospital_Name])) //This is the first choice
VAR LessChoice1 =EXCEPT(Choices, Choice1) //this is supposed to give me all but the first choice
Return
Calculate (SUM(HospMeasures[Metric Value]), Filter(HospProviders,HospProviders[Hospital_Name] = TopN(1,LessChoice1))) //This should now take the first remaining value.

it is "valid" dax.  I can't add a "values" for examplee at LessChoice1...but I really don't quite know what that looks like.  Maybe I should put it in a column and see.

Tom

And this gives me a frown.  😞

[ThomasDay]

The issue was that not every row had values for each company--so I needed to trap for the presence of a value for every row.!  My bad and thanks for the help.  It wasn't clear, but now that I see it, it makes sense.  The pivot table will only make a row if there is data--and since not every company has values for every row, it's pretty dynamic.  When the company didn't have a row value, the slicer "list" was shorter...and the TOPN was looking for something that wasn't there.   So I need to trap for a value, and if there isn't one--put in a zero.  The formatting of the whole column is now "lost"--so I need to figure that out, but it was pretty sneeky.

Tom

[ThomasDay]

I wonder how/if the slicer list changes for every row...and how I'll keep the companies in the right column.  If company 2 doesn't have a value for a pivot table row, and Company 3 does--is Company 3's name now in the second spot?  Hmmm, this is getting tricky.  I'll have to do some experimenting to see how the behavior of the slicer list works.

I'm getting foggy...best to pick this up later.

Any thoughts are welcome,

Tom

PS: got a note from the 😞 submission that the bug I've found is being worked on and to stay tuned.