cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smore
Frequent Visitor

Random Filter or Return Random Variable inside measure.

NameStrIndex
Aabc1
Adef2
Ahij3
Blmn4
Bopq5
Cxyz6

I have a measure (MyMeasure) which concatenates the [Str] column if [Name] has one value. 

 

 

 

MyMeasure = IF(HASONEVALUE('Table'[Name]),CONCATENATEX('Table','Table'[Str],,'Table'[Index],ASC),
BLANK())

 


I have a tooltip which returns MyMeasure.
The tooltip is linked to a scatter chart which when hovering cursor over Bubble A, it returns "abcdefhij" and for Bubble B: "lmnopq".

Since the measure checks if 'Table' hasonevalue I encounter an issue if two bubbles on the scatter chart overlap. However, If Bubble A and Bubble B overlap, I want the tooltip to display the results for Bubble A briefly and then Bubble B. 

I created an ID column like so: 

NameStrIndexID
Aabc11
Adef21
Ahij31
Blmn42
Bopq52



 

MyMeasure2 = 

VAR A = IF(HASONEVALUE('Table'[Name]),CONCATENATEX('Table','Table'[Str],,'Table'[Index],ASC),BLANK())

VAR B = CALCULATE(CONCATENATEX('Table', 'Table'[Str],,'Table'[Index],ASC), FILTER('Table', 'Table'[ID] = MAX('Table'[ID])))

VAR C = IF(HASONEVALUE('Table'[Name]), A, B)

RETURN C

 


I adjusted the above measure to now filter the 'Table' to the MAX ID if there are multiple results. The measure now returns a result when bubbles overlap but always the same result (the [Name] which has the max [ID]). How can I adjust the measure so that it randomly selects which [Name] to filter and concatenate. This way, if Bubble A & B overlap, I can however over and see the results for A, move cursor off and potentially see Results for Bubble B if I hover back over. 

3 REPLIES 3
MFelix
Super User
Super User

Hi @smore ,

 

This is not possible since when you have the Name in your scatter chart each value is separated so the context of your measure is the buble that is on top so only A or B this can be seen on the image below. has you can see the image B point is on top so the context is B and not A:

 

MFelix_0-1633038623194.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



smore
Frequent Visitor

Thanks for your response. If I hover over the union region for Bubble A and Bubble B I am able to return the concatenated results for Bubble A or Bubble B  if I change Var B's Filter from MAX() to MIN() in the measure below. 

MyMeasure2 = 

VAR A = IF(HASONEVALUE('Table'[Name]),CONCATENATEX('Table','Table'[Str],,'Table'[Index],ASC),BLANK())

VAR B = CALCULATE(CONCATENATEX('Table', 'Table'[Str],,'Table'[Index],ASC), FILTER('Table', 'Table'[ID] = MAX('Table'[ID])))

VAR C = IF(HASONEVALUE('Table'[Name]), A, B)

RETURN C

 

So it seems possible but I am not sure what the best DAX is to go about returning results for each bubble randomly or toggling the results. 

Below is my crude attempt at randomly returning results for Bubble A or Bubble B. However, the RAND() function is only calculated if the data is changed or if Power BI Desktop is refreshed. The Microsoft Docs says: 

  • "RAND is always recalculated if the function is used in the definition of a measure."

However, in MyMeasureRandom below, it does not recalculate when hovering over different data bubbles so the below function does not work to randomly use VAR B or VAR C. 

MyMeasureRandom = 

VAR A = IF(HASONEVALUE('Table'[Name]),CONCATENATEX('Table','Table'[Str],,'Table'[Index],ASC),BLANK())

VAR B = CALCULATE(CONCATENATEX('Table', 'Table'[Str],,'Table'[Index],ASC), FILTER('Table', 'Table'[ID] = MAX('Table'[ID])))
VAR C = CALCULATE(CONCATENATEX('Table', 'Table'[Str],,'Table'[Index],ASC), FILTER('Table', 'Table'[ID] = MIN('Table'[ID])))
VAR D = RAND()
VAR E = RAND()
VAR F = IF(D>E, B, C)
VAR G = IF(HASONEVALUE('Table'[Name]), A, F)

RETURN G

 Perhaps I should create a new post asking how to randomly return Variables in a measure?

Hi @smore ,

 

Don't really understand what you refer in the first sentance but believe that using the MIN and the MAX you are forcing the context to be A or B so you are not really getting the result of the bubble you are overing but instead forcing the context to change and return other value that can be checked using selectedvalue instead of the MAX or MIN, again did not quite get you sentance.

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.