Forum Discussion

natasha519's avatar
natasha519
Helper II
4 years ago

Help with Measure

I have a measure with this formula:

 

Numerator = if(or(or(or(or(or(SELECTEDVALUE('Jurisdiction Filter'[Recipient])="California",SELECTEDVALUE('Jurisdiction Filter'[Recipient])="Hawaii"),SELECTEDVALUE('Jurisdiction Filter'[Recipient])="Iowa"),SELECTEDVALUE('Jurisdiction Filter'[Recipient])="Minnesota"),SELECTEDVALUE('Jurisdiction Filter'[Recipient])="Oklahoma"),SELECTEDVALUE('Jurisdiction Filter'[Recipient])="Puerto Rico"),Countrows(Filter(DOSE_Timeliness,DOSE_Timeliness[Timeliness Icon Status]=1)),countrows(Filter(DOSE_Timeliness,DOSE_Timeliness[Timeliness Icon Status]=1))
 
How can I incorporate another filter within this measure to say where the value from another table is not blank ('LatestData[name])?

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    natasha519 , should work if tables are joined

     


    Numerator = calculate(if(or(or(or(or(or(SELECTEDVALUE('Jurisdiction Filter'[Recipient])="California",SELECTEDVALUE('Jurisdiction Filter'[Recipient])="Hawaii"),SELECTEDVALUE('Jurisdiction Filter'[Recipient])="Iowa"),SELECTEDVALUE('Jurisdiction Filter'[Recipient])="Minnesota"),SELECTEDVALUE('Jurisdiction Filter'[Recipient])="Oklahoma"),SELECTEDVALUE('Jurisdiction Filter'[Recipient])="Puerto Rico"),Countrows(Filter(DOSE_Timeliness,DOSE_Timeliness[Timeliness Icon Status]=1)),countrows(Filter(DOSE_Timeliness,DOSE_Timeliness[Timeliness Icon Status]=1)) ) , filter('LatestData', not(isblank ('LatestData'[name]))) )

    • natasha519's avatar
      natasha519
      Helper II
      4 years ago

      I am getting an unexpected expression error as soon as I insert a comma

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi natasha519 

     

    First, I think you can use this rather thatn using many OR in the formula:

    Numerator =
IF (
    SELECTEDVALUE ( 'Jurisdiction Filter'[Recipient] )
        IN { "California", "Hawaii", "Iowa", "Minnesota", "Oklahoma", "Puerto Rico" },
    COUNTROWS (
        FILTER ( DOSE_Timeliness, DOSE_Timeliness[Timeliness Icon Status] = 1 )
    ),
    COUNTROWS (
        FILTER ( DOSE_Timeliness, DOSE_Timeliness[Timeliness Icon Status] = 1 )
    )
)

     

    Where do you want to add a new filter, in the result or in the statement?

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi natasha519 ,

     

    Numerator =
VAR _count =
    CALCULATE (
        COUNTROWS ( DOSE_Timeliness ),
        FILTER ( DOSE_Timeliness, DOSE_Timeliness[Timeliness Icon Status] = 1 ),
        FILTER ( 'LatestData', NOT ( ISBLANK ( 'LatestData'[name] ) ) )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Jurisdiction Filter'[Recipient] )
            IN { "California", "Hawaii", "Iowa", "Minnesota", "Oklahoma", "Puerto Rico" },
        _count,
        _count
    )

    One wired thing in your formula, the if function returns the same count when it gets right or wrong?

     

    Best Regards,

    Jay