Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculated average value isnt correct

H2SO4 mg/l = VAR H2SO4_apl =     CALCULATE (         SUMX ( ImportOfLIMS, ImportOfLIMS[H2SO4] ),         ImportOfLIMS[SAMPLE_GROUP_NAME] = "Acid Plant"     ) VAR isblankH2SO4 =     IF (      ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Anonymous , that's interesting. Try adding some more debug code. I'm interested to see if the two methods you use to refer to [H2SO4 kg/d] give the same result.

     

     

        VAR H2SO4measure = [H2SO4 kg/d]
    VAR H2SO4column = CALCULATE ( SUMX ( ImportOfLIMS, 'ImportOfLIMS'[H2SO4 kg/d] ) )
    VAR Bleed = [APL Bleed m3/day]
    VAR H2SO4mg = [H2SO4 mg/l]

    RETURN
        FORMAT(MINX(MostRecentDates, [Date]), "YYYYMMDD") 
        & " ; " & FORMAT(MAXX(MostRecentDates, [Date]), "YYYYMMDD") 
        & " ; " & MINX(MostRecentDates, [@H2SO4]) 
        & " ; " & MAXX(MostRecentDates, [@H2SO4])
        & " ; " & FORMAT(Result, "0.00")
        & " ; " & FORMAT(H2SO4measure, "0.00")
        & " ; " & FORMAT(Bleed, "0.00")
        & " ; " & FORMAT(H2SO4mg, "0.00")

     

     

      If 'ImportOfLIMS'[H2SO4 kg/d] is actually a measure and not a column, then I believe you can change the [Avg_10_H2SO4_(kg/d)] to refer to it like this below. ADDCOLUMNS is an iterator and will calculate the expression for "@H2SO4" for every row in the AllDatesPrior table variable, so there is no need for a SUMX or CALCULATE.

     

     

        VAR AllDates_H2SO4 =
        ADDCOLUMNS (
            AllDatesPrior,
            //"@H2SO4", CALCULATE ( SUMX ( ImportOfLIMS, 'ImportOfLIMS'[H2SO4 kg/d] ) )
            "@H2SO4", [H2SO4 kg/d]
        )

     

     

     Incidentally, from the raw data, what actually is the correct result for 21 Jun?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Anonymous Glad it worked! 

I'm not sure why the CALCULATE SUMX didn't work, either. The CALCULATE function is used to change the context of the expression, but in this case there were no changes to filters applied. The SUMX also is an iterator and changes the context, so a suspect the combination of ADDCOLUMNS, CALCULATE and SUMX together has ended up with a context that is different to expected. 

To delve further you could add more debug code to examine what the CALCULATE-SUMX produces. For example, change it to each of these to check out what data it is producing.

CALCULATE ( MINX( ImportOfLIMS, 'ImportOfLIMS'[Date] ) )
CALCULATE ( MAXX( ImportOfLIMS, 'ImportOfLIMS'[Date] ) )
CALCULATE ( COUNTROWS( ImportOfLIMS))
CALCULATE ( MINX( ImportOfLIMS, 'Calendar'[Date] ) )
CALCULATE ( MAXX( ImportOfLIMS, 'Calendar'[Date] ) )

 

Yes, ADDCOLUMNS is an iterator function.

ADDCOLUMNS – DAX Guide

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I will try and further debug this one soon. at leastnow  I can continue building 😃 

 

for now, thanks a lot for your help. appreciate it very much!