Ok I have a very complicated DAX formula that I would like to optimize. Just to be clear, I may use the word Sample in place of Denominator, but I am essentially referring to the same thing.
2 Fact Tables (1 to many relationship with bi-directional cross filtering):
- FormSubmit is 1 row per submitted form
- FormSubmitItems is 1 row per item populated per form related based on FormSubmitID
There are 100's of different types forms, numerators, and denominators being submitted
1 Dim Table (1 to many relationship with FormSubmitItems with bi-directional cross filtering)
- FormItemCategories - 1 row per unique FormItem
- Indicates that either the form item is a numerator or a denominator
- So every numerator Form Item would have an associated denominator Form Item to divide by per Form
Not every numerator has a denominator, and therefore should be divided by 1 or the # of forms for the numerator e.g. FormCount
Some forms have 2 denominators or subgroups indicated by the FormSubGroup, where 1 sub group of numerators is associated with 1 denominator, and another sub group of numerators is associated with the other denominator.
There is also 1 other special scenario: some of the denominators have a special multiplier indication by user via a dropdown called the UnitType.
So to put this all together:
I currently am calculating as shown below by the formula
- EvaluateFormSubGroupSample - Denominator for each numerator in a subgroup
- EvaluateFormSample - Denominator for each numerator in a NON subgroup form (can probalby be eliminated and just use above
- EvaluateUnitTypeSample - Get the multiplier for the denominators that needed it
Then to put it all together
- Evaluate sample = if subgroup sample is blank then use form sample
- if unittype mulltiplier is blank then carry down sample as is, otherwise use multiplier
- If all of these turn up blank then just use the FormCount as the denominator or Sample.
Since the calculation/filter context needs to be performed at the subgroup level I must use a SUMX per SubGroup of FormSubmitItems nested in another SUMX per Form Submition - FormSubmitID. Or at least that is the only thing I could get to work for these fact tables as I am working at that level of filter context and....
This allows the user to see the score by one or more FormItems that may have different denominators and the filter context will appropriately weight the score across the board where needed either at the granular per Item level or as an overall system wide score.
Performance is not scaling well, Help! 🙂
DAX formula below
The bi-directional cross filtering mentioned before is a must for the user to be able to see all levels of granularity and just does not calculate correctly and does not help performance if i cut off. I've tried just using it at the measure level but that does not help either as I just end up having to add to too many measures any way. So guessing it has more to do with the nested SumX and multiple Calculate function and hoping there is a better way. I will continue to think through and will post if I come up with anything, but any help would be greatly appreciated.
SampleSize =
SUMX(DISTINCT(FormSubmit[FormSubmitID]),
VAR RelatedFormSubmitItem = SUMX(DISTINCT(FormSubmitItems[FormSKFormSubGroupIndex]),
VAR EvaluateFormSample = CALCULATE(SUM(FormSubmitItems[Value]),ALLEXCEPT(FormItemCategories,FormItemCategories[FormID]),FormItemCategories[Input]="Denominator",FormItemCategories[Override]=0)
VAR EvaluateFormSubGroupSample = CALCULATE(SUM(FormSubmitItems[Value]),ALLEXCEPT(FormItemCategories,FormItemCategories[FormSubGroup]),FormItemCategories[Input]="Denominator",FormItemCategories[Override]=0)
VAR EvaluateUnitTypeSample = CALCULATE(MAX(DimFormItemPicker[IDValue]),ALLEXCEPT(FormItemCategories,FormItemCategories[FormID]),FormSubmitItems[InputType]=3)
VAR EvaluateSample = SWITCH(BLANK(),EvaluateFormSubGroupSample,EvaluateFormSample,EvaluateFormSubGroupSample)
VAR Sample = SWITCH(BLANK(),EvaluateUnitTypeSample,EvaluateSample,EvaluateSample*EvaluateUnitTypeSample)
VAR FormCount = CALCULATE(SUM(FormSubmit[FormSubmitCount]),ALLEXCEPT(FormItemCategories,FormItemCategories[FormID]))
Return
SWITCH(BLANK(),Sample,FormCount,Sample))
Return
RelatedFormSubmitItem)
