The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have long struggled with the summing up of measures at every level of complex hierarchies that cover multiple dimensions. So of course I've dabbled with the SUMX function becuase that fixes a lot of things. But I've always struggled with how to understand what to put as the first parameter in SUMX. It seems that no matter what value I put for the first parameter of SUMX, sometimes it is good and someties bad.
So I started doing this...
SUMX (
DATATABLE ( "1", INTEGER, { { 1 } } ),
CALCULATE (
COUNTROWS ( 'Sales Agg Date Store' ),
'Sales Agg Date Store'[Sales Above Threshold Flag] = TRUE ()
)
)
The important part is the first parameter of SUMX there. It is just a single value single column single row static table. When I write my SUMX like that it adds up among every hierarchy I throw at it. Miraculous. But seems like I'm maybe missing the point of SUMX by hacking it like this. Or am I? Is there a better way to do this?
Solved! Go to Solution.
From what I can see, the SUMX portion of this formula does nothing compared with removing SUMX all together. The first parameter is a table; a physical table or a virtual table that is typically part of the model (your table is not part of the model). The table will normally/necessarily have a relationship to the rest of the model or lineage (a virtual relationship in the case of a virtual table) that propagates filters between the first table parameter and the rest of the model, so that the second parameter can do its job.
it is not possible to answer your specific query because you have not provided any details of the model or the problems you are facing. You may find my article here useful. https://exceleratorbi.com.au/use-sum-vs-sumx/
Thanks @MattAllington ... You're right. This measure changed so many times, I totally worked myself in and out of needing a SUMX.
Thanks for the extra set of eyes!!!
From what I can see, the SUMX portion of this formula does nothing compared with removing SUMX all together. The first parameter is a table; a physical table or a virtual table that is typically part of the model (your table is not part of the model). The table will normally/necessarily have a relationship to the rest of the model or lineage (a virtual relationship in the case of a virtual table) that propagates filters between the first table parameter and the rest of the model, so that the second parameter can do its job.
it is not possible to answer your specific query because you have not provided any details of the model or the problems you are facing. You may find my article here useful. https://exceleratorbi.com.au/use-sum-vs-sumx/
User | Count |
---|---|
25 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |