Forum Discussion

DionTN's avatar
DionTN
Helper II
4 years ago

Create calculations based on other function

See my table below:

I have a table which contains appointmentID's and combined it with companyID's.

https://drive.google.com/file/d/1GDrzHPvDbK-AN6tOk_XreMb5JrYYzUGp/view?usp=sharing

 

Now i made a function to check all the first measurements from a selected period, from a selected company for every user:

 

FirstValue =
VAR selectie =
    SELECTEDVALUE ( company[Name] )
VAR EersteWaarde =
    CALCULATE (
        MIN ( usermeasurements[AppointmentId] ),
        FIRSTDATE ( usermeasurements[OnderzoekVerichtOp] ),
        FILTER ( usermeasurements, usermeasurements[users.company.Name] = selectie )
    )
RETURN
    EersteWaarde

 

This gives this table when i have correct selections:

Now i want to get the rows with those appointment ID's to get an average of some columns.I tried this, but now it only takes the lowst first value which is 1:

 

GemiddeldeLichaamssamenstellingEerste =
VAR selectie =
    ADDCOLUMNS (
        VALUES ( company[Name] ),
        "EersteWaarde",
            CALCULATE (
                MIN ( usermeasurements[AppointmentId] ),
                FIRSTDATE ( usermeasurements[OnderzoekVerichtOp] )
            )
    )
VAR LS =
    CALCULATE (
        AVERAGE ( usermeasurements[BodyCompositionOverall] ),
        TREATAS (
            selectie,
            usermeasurements[users.company.Name],
            usermeasurements[appointments.AppointmentNumber]
        )
    )
RETURN
    COALESCE ( LS / 10, 0 )

 

What can I do to get all the firstValues as input for my average calculations? Also when i change the company filter it needs to change the first values.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    DionTN , try a measure like

     

    CALCULATE (
    AVERAGE ( usermeasurements[BodyCompositionOverall] )
    Filter(Table, Table[appointments.AppointmentNumber] =calculate( firstnonblank(Table[Date], Min(Table[appointments.AppointmentNumber]))
    , filter(allselected(Table),Table[Company] = max(Table[Company])
    // optionally add
    && Table[Company] = selectedvalue(Table[Company])
    ))))

    • DionTN's avatar
      DionTN
      Helper II
      4 years ago

      amitchandak 

      Unfortunately its not working. I tried this based on your example:

       

       

      AvgBody =
CALCULATE (
    AVERAGE ( usermeasurements[BodyCompositionOverall] ),
    FILTER (
        usermeasurements,
        usermeasurements[AppointmentId]
            = CALCULATE (
                FIRSTNONBLANK (
                    usermeasurements[OnderzoekVerichtOp],
                    MIN ( usermeasurements[AppointmentId] )
                ),
                FILTER (
                    ALLSELECTED ( usermeasurements ),
                    usermeasurements[users.company.Name]
                        = MAX ( usermeasurementsSCND[users.company.Name] )
                        && usermeasurements[users.company.Name]
                            = SELECTEDVALUE ( usermeasurementsSCND[users.company.Name] )
                )
            )
    )
)