Error with AverageX

[nehajadhav166]

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

[tamerj1 · Accepted solution]

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

[nehajadhav166]

@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 · Accepted solution]

@nehajadhav166 
I mean 

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

[nehajadhav166]

@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 · Accepted solution]

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

[nehajadhav166]

@tamerj1 , I tried but still same error.

Thanks,

Neha

[tamerj1]

@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]
            ),
        ""
    )

[tamerj1]

@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]
            ),
        ""
    )