Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Name | Str | Index |
A | abc | 1 |
A | def | 2 |
A | hij | 3 |
B | lmn | 4 |
B | opq | 5 |
C | xyz | 6 |
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:
Name | Str | Index | ID |
A | abc | 1 | 1 |
A | def | 2 | 1 |
A | hij | 3 | 1 |
B | lmn | 4 | 2 |
B | opq | 5 | 2 |
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.
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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |