Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am using the matrix visualization to create a matrix bubble chart using SVG, similar to this post Matrix Bubble Chart By DAX . The columns of the table are vendors, the rows are product type and item, and the summary value is revenue, as follows:
Product Type | Item | Vendor A | Vendor B | Vendor C |
clothing | shoes | 5 | 20 | |
clothing | socks | 10 | 40 | 30 |
groceries | apples | 15 | 25 | |
groceries | oranges | 20 | 10 |
So the largest value in this table is 40.
I want to filter the table dynamically for different vendors using slicers, and to scale the maximum bubble size in the entire chart to the largest value in the filtered matrix.
I've been able to calculate the radius for the SVG circle using the following DAX:
Radius =
VAR totalRevenue = SUM('Sales'[Revenue])
VAR maxRevenue = MAXX(ALLSELECTED('Sales'[Vendor]), totalRevenue)
VAR circleRadius = DIVIDE(totalRevenue, maxRevenue, 0)
RETURN
circleRadius
The problem I'm having is that radius for the same value of totalRevenue is different on a row-by-row basis because of the MAXX function, which finds the maximum value for each row. If I try replacing MAXX with MAX, I get an error:
Function 'MAX' does not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.
I thought perhaps I could create a dynamic table using slicers as the basis for my calculation. In searching this forum, though, I came across this post Create dynamic DAX table filtered by slicer from another table in which @MattAllington says "You cannot create a dynamic table based on a slicer." I've read Matt's books and newsletters on DAX, and I trust that he knows what he's talking about.
Is there another way to do what I want?
Solved! Go to Solution.
Hi @v-yiruan-msft ,
The problem I'd been having was that I would get different bubble sizes for the same value on different rows. Using ALLSELECTED('table') with no columns specified seems to accomplish what I wanted.
Hi @CloudHerder ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create the measures as below:
SRevenue = SUM('Sales'[Revenue])
Chart =
VAR MAXR =
MAXX (
FILTER (
ALLSELECTED ( 'Sales' ),
'Sales'[Product Type] = SELECTEDVALUE ( 'Sales'[Product Type] )
&& 'Sales'[Item] = SELECTEDVALUE ( 'Sales'[Item] )
),
[SRevenue]
)
RETURN
"data:image/svg+xml;utf8," & "
<svg xmlns='http://www.w3.org/2000/svg' height='100' width='100'>
<line x1='0' y1='50' x2='100' y2='50' stroke='black' stroke-width='0.5' />
<line x1='50' y1='0' x2='50' y2='100' stroke='black' stroke-width='0.5' />
<circle cx='50' cy='50' r='" & 40 * [SRevenue] / MAXR & "' fill='"
& IF ( [SRevenue] > 20, "Darkcyan", "Tomato" ) & "'/>
<text x='50' y='50' font-size='20' text-anchor='middle' dominant-baseline='middle'>" & [SRevenue] & "</text>
</svg> "
2. Create a matrix visual
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hello @v-yiruan-msft and thank you for taking the time to reply to my question. I have downloaded the .pbix file you provided. It doesn't do what I'm looking for, though. To illustrate, I've modified your file to show three different scenarios:
I've provided 3 corresponding versions of the bubble chart you provided. All 3 work well enough if I select a row, but if any filters span multiple rows, the results are different.
The version using ALLSELECTED comes closest to what I want, which is to maximize the largest bubble size based on the current filter context.
Here's a link to my version:
https://1drv.ms/u/s!AgIj2L_vt8WrhtVK67K3pz6l6b2rSw?e=hCMxNe
Hi @CloudHerder ,
Thank you for the further clarification, did you get the final result you wanted? Is there anything else you need help with regarding this post? If not, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.
Best Regards
Hi @v-yiruan-msft ,
The problem I'd been having was that I would get different bubble sizes for the same value on different rows. Using ALLSELECTED('table') with no columns specified seems to accomplish what I wanted.
Product Type | Item | Vendor A | Vendor B | Vendor C |
clothing | shoes | 5 | 20 | |
clothing | socks | 10 | 40 | 30 |
groceries | apples | 15 | 25 | |
groceries | oranges | 20 | 10 |
So the largest value in this table is 40.
measure =
MAXX(
SUMMARIZECOLUMNS(table[Product Type],table[Item],table[Vendor]),
CALCULATE([MEASURE FOR REVNUE])
)
=40
Thanks for the reply @vapid128 . I tried the solution suggested, but I'm getting an error:
Error Message:
MdxScript(Model) (45, 6) Calculation error in measure 'Table'[Max Revenue]: SummarizeColumns() and AddMissingItems() may not be used in this context.
I should have also mentioned that, ideally, I would like to use a Fields Parameter to select the Row dimensions I want to display.