Sum up occurrencies of selectedvalues of 20 filters

DAX DAX Calculations
1

In order to make some generic BOM creation based on products, the enduser shall be able to choose converned untis intuitively.
I created 22 individual tables with filters, where the unser can choose from

To make some BOM calculations, I need the amount of the same products selected from 20 filters

Report1
Report1.jpg2229×1316 939 KB

model
model.jpg2435×748 468 KB

Each choice for each filter is a measure “selected length X” ; e.g. for Filter 10

sel_length10 = SELECTEDVALUE('Zeilengerät 10'[Länge])

I currently have 15 Product types for which I wanted to calculate the sum of their occurrencies due to unser selection

So I have 15 Measures similar to this one for produt type MIAMI 145:

Amount Miami145 = 
VAR selection ="Miami 145"
VAR res1 = if([sel_length1]=selection;1;0)
VAR res2 = if([sel_length2]=selection;1;0)
VAR res3 = if([sel_length3]=selection;1;0)
VAR res4 = if([sel_length4]=selection;1;0)
VAR res5 = if([sel_length5]=selection;1;0)
VAR res6 = if([sel_length6]=selection;1;0)
VAR res7 = if([sel_length7]=selection;1;0)
VAR res8 = if([sel_length8]=selection;1;0)
VAR res9 = if([sel_length9]=selection;1;0)
VAR res10 = if([sel_length10]=selection;1;0)
VAR res11 = if([sel_length11]=selection;1;0)
VAR res12 = if([sel_length12]=selection;1;0)
VAR res13 = if([sel_length13]=selection;1;0)
VAR res14 = if([sel_length14]=selection;1;0)
VAR res15 = if([sel_length15]=selection;1;0)
VAR res16 = if([sel_length16]=selection;1;0)
VAR res17 = if([sel_length17]=selection;1;0)
VAR res18 = if([sel_length18]=selection;1;0)
VAR res19 = if([sel_length19]=selection;1;0)
VAR res20 = if([sel_length20]=selection;1;0)
VAR result= res1+res2+res3+res4+res5+res6+res7+res8+res9+res10+res11+res12+res13+res14+res5+res16+res17+res18+res19+res20
return
result

I now have a huge performance issue, because each new product type measure causes the report to take 5 seconds longer to refresh

Is there any other trick, that I can find out the occurrencies for each product type faster?
Use SWITCH instead of IF(), MIN() insteas od SELECTEDVALUE(); or another measure that generates a temporary table as VAR where I can make a CALCULATE(COUNTROWS(temptable);length=“Miami 145” or similar.

I am somehow in a dead end right now it seems.

THANKS a lot for taking the tim to dig into this & br,
Alex

2

This looks little too complex to me and I’m sure some of the performance comes from having so many tables and relationships to those tables.

Is there any reason why you feel you need that many tables? What couldn’t think all just be in one table with many columns/dimensions that you could have a slicers.

It seems like all the tables are exactly the same so to me, you should be able just to create one master table somehow

You could still have a formula similar to what you have there but if you just worked over one master table I’m sure it will be faster.

You could also use SWITCH/TRUE to see which ones are turned on.

I’m hesitant to go any further into a solution here because I genuinely believe that there must be a way to do this without all those tables.

Can you have a think about simplification here. Highly recommend it and will likely solve your issues quite quickly.

Thanks
Sam