Forum Discussion

nehajadhav166's avatar
nehajadhav166
Resolver I
4 years ago
Solved

Error with AverageX

Hi Experts,

 

I have created measure :

Cpk =
Var selected_Value=SELECTEDVALUE(TestData[Name])
Var selected_Char=SELECTEDVALUE(TestData[Characteristic.Name])
Var selected_maxdate=IF (ISFILTERED ( 'TestData'[Date] ),MAX ( 'TestData'[Date] ))
Var selected_mindate=IF (ISFILTERED ( 'TestData'[Date] ),MIN ( 'TestData'[Date] ))
Var _tab = values(TestData[Date])
Var selected_Machine=SELECTEDVALUE(TestData[Source.Name.1])
Var selected_adpater=SELECTEDVALUE(TestData[TestHeader.testAdapter.serialNo])
Return
SWITCH( TRUE(),
SEARCH( "loopback", selected_Value,,0) <> 0 , SELECTCOLUMNS(RFLoopback,"CPK",AVERAGEX(filter(RFLoopback, RFLoopback[Cpk]<>BLANK() && RFLoopback[Characteristic.Name]=selected_Char && RFLoopback[Source.Name.1]=selected_Machine && RFLoopback[TestHeader.testAdapter.serialNo]=selected_adpater && DATESBETWEEN(RFLoopback[Date],selected_mindate,selected_maxdate) ),RFLoopback[Cpk])),
""
)
 
When I try to map this measure in chart its giving me below error:

This error started occuring when I added filter for selected values.

I am not sure what is wrong.Can you help here?

 

Thanks in advance.

 

Thanks,

Neha

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi nehajadhav166 
    Calendar funtion works normally inside measures. You can also replace it with DATESBETWEEN and see if the result changes.

  • tamerj1's avatar
    tamerj1
    4 years ago

    nehajadhav166 
    I mean 

    && RFLoopback[Date] IN DATESBETWEEN ( RFLoopback[Date], selected_mindate, selected_maxdate )

7 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    nehajadhav166 

    Please try

    Cpk =
VAR selected_Value =
    SELECTEDVALUE ( TestData[Name] )
VAR selected_Char =
    SELECTEDVALUE ( TestData[Characteristic.Name] )
VAR selected_maxdate =
    IF ( ISFILTERED ( 'TestData'[Date] ), MAX ( 'TestData'[Date] ) )
VAR selected_mindate =
    IF ( ISFILTERED ( 'TestData'[Date] ), MIN ( 'TestData'[Date] ) )
VAR _tab =
    VALUES ( TestData[Date] )
VAR selected_Machine =
    SELECTEDVALUE ( TestData[Source.Name.1] )
VAR selected_adpater =
    SELECTEDVALUE ( TestData[TestHeader.testAdapter.serialNo] )
RETURN
    SWITCH (
        TRUE (),
        SEARCH ( "loopback", selected_Value,, 0 ) <> 0,
            AVERAGEX (
                FILTER (
                    RFLoopback,
                    RFLoopback[Cpk] <> BLANK ()
                        && RFLoopback[Characteristic.Name] = selected_Char
                        && RFLoopback[Source.Name.1] = selected_Machine
                        && RFLoopback[TestHeader.testAdapter.serialNo] = selected_adpater
                        && DATESBETWEEN ( RFLoopback[Date], selected_mindate, selected_maxdate )
                ),
                RFLoopback[Cpk]
            ),
        ""
    )
    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      nehajadhav166 

      Please try

      Cpk =
VAR selected_Value =
    SELECTEDVALUE ( TestData[Name] )
VAR selected_Char =
    SELECTEDVALUE ( TestData[Characteristic.Name] )
VAR selected_maxdate =
    IF ( ISFILTERED ( 'TestData'[Date] ), MAX ( 'TestData'[Date] ) )
VAR selected_mindate =
    IF ( ISFILTERED ( 'TestData'[Date] ), MIN ( 'TestData'[Date] ) )
VAR selected_Machine =
    SELECTEDVALUE ( TestData[Source.Name.1] )
VAR selected_adpater =
    SELECTEDVALUE ( TestData[TestHeader.testAdapter.serialNo] )
RETURN
    SWITCH (
        TRUE (),
        SEARCH ( "loopback", selected_Value,, 0 ) <> 0,
            AVERAGEX (
                FILTER (
                    RFLoopback,
                    RFLoopback[Cpk] <> BLANK ()
                        && RFLoopback[Characteristic.Name] = selected_Char
                        && RFLoopback[Source.Name.1] = selected_Machine
                        && RFLoopback[TestHeader.testAdapter.serialNo] = selected_adpater
                        && RFLoopback[Date] IN CALENDAR ( selected_mindate, selected_maxdate )
                ),
                RFLoopback[Cpk]
            ),
        ""
    )
  • nehajadhav166's avatar
    nehajadhav166
    Resolver I
    4 years ago

    tamerj1 , Error seems to be gone but I dont get correct calculeted value for Cpk.

    Looks like caldender function not considering date range. If I user calender function seperatly as measure I get similar error as above.

    Do I have to conver any column/value?

     

    Thanks,

    Neha

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      Hi nehajadhav166 
      Calendar funtion works normally inside measures. You can also replace it with DATESBETWEEN and see if the result changes.

  • nehajadhav166's avatar
    nehajadhav166
    Resolver I
    4 years ago

    tamerj1  yes in original measure I used same and then I am getting error.Is there any other way yo handle this?

     

    Thanks,

    Neha

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      nehajadhav166 
      I mean 

      && RFLoopback[Date] IN DATESBETWEEN ( RFLoopback[Date], selected_mindate, selected_maxdate )