how to retrieve a scalar value of a text field for a measure

[Anonymous]

I have this kind of a table and I want the average time spent and the conditions are as follows:

     if the category is in first_six or second_six ignore the time spent records which exceeds 4 

     else average of all the records

[v-easonf-msft]

Hi, @Anonymous 

You can try formula as below:

Average time spent =
VAR tab =
    FILTER (
        'Table',
        NOT ( 'Table'[month_category]
            IN { "first_six", "second_six" }
            && 'Table'[time spent] > 4 )
    )
RETURN
    AVERAGEX ( tab, 'Table'[time spent] )

Best Regards,
Community Support Team _ Eason

[Tanushree_Kapse]

Hi @Anonymous ,

Try this Dax:

Average time spent= 
IF(AND(OR(Table[month_category]= "first_six ", Table[month_category]= "second_six "),Table[time_spent] > 4), AVERAGE(Table[time_spent]), blank())

Mark this as a solution if it answers your question. Kudos are always appreciated.

Thanks

Check out the data gallery: https://community.powerbi.com/t5/Data-Stories-Gallery/Marvel-Superheroes-Dashboard/td-p/2292663

[Anonymous]

Since I'm creating a measure "(OR(Table[month_category]= "first_six ", Table[month_category]= "second_six ")" this part should return a scalar value

[Tanushree_Kapse]

Hi @Anonymous ,
Try this for a measure:

Average time spent= 
IF(

AND(
OR(SELECTEDVALUE(Table[month_category])= "first_six ", 
SELECTEDVALUE(Table[month_category])= "second_six "),
SELECTEDVALUE(Table[time_spent]) > 4), AVERAGE(Table[time_spent]),

blank()
)

Mark this as a solution if it answers your question. Kudos are always appreciated.

Thanks

Check out the data gallery: https://community.powerbi.com/t5/Data-Stories-Gallery/Marvel-Superheroes-Dashboard/td-p/2292663