Creating a measure with filters

[Anonymous]

I am creating a card visual that has to display the gestational age based on the type of program,

>If it is prenatal it should display the appropriate gestational age on patient level and on population level it should display the average of the gestational age

>If the  program is not prenatal then the card should display nothing

Using my current logic , I am able to get the minimum gestational age but not the average.This the logic that I am using for the card currently,

Gestational Age-Filter = IF(Overview[Gestational Age - Copy]<>BLANK(),INT((Overview[Gestational Age - Copy])/7)&"W"& ROUND(MOD((Overview[Gestational Age - Copy]),7),0)&"D"),and this one displays the minimum gestational age,I can't use an average formula on top of this calculated column,since this is a string.

I also tried using a measure,

Gestational_age = calculate((INT(AVERAGE(Overview[Gestational Age - Copy])/7)&"W "& ROUND(MOD(AVERAGE(Overview[Gestational Age - Copy]),7),0)&"D"))

Now when I use this I am able to see W D being displayed on the card,when the gestational age is not available.

How can I handle this?

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

Gestational_age =
IF (
    CALCULATE (
        COUNTROWS ( Overview ),
        Overview[Gestational Age - Copy] <> BLANK ()
    ) = 0,
    BLANK (),
    CALCULATE (
        (
            INT ( AVERAGE ( Overview[Gestational Age - Copy] ) / 7 ) & "W "
                & ROUND ( MOD ( AVERAGE ( Overview[Gestational Age - Copy] ), 7 ), 0 ) & "D"
        )
    )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[Anonymous]

Thanks a lot ,it worked well.Can you please explain how this works.I want to know more so that I can apply this with other problems as well.