topic Re: Help with Measure in DAX Commands and Tips

Help with Measure

natasha519 — Thu, 02 Dec 2021 16:10:18 GMT

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])?

Re: Help with Measure

amitchandak — Thu, 02 Dec 2021 16:34:17 GMT

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]))) )

Re: Help with Measure

natasha519 — Thu, 02 Dec 2021 16:38:02 GMT

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

Re: Help with Measure

VahidDM — Fri, 03 Dec 2021 05:40:04 GMT

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/

Re: Help with Measure

Anonymous — Tue, 07 Dec 2021 09:50:52 GMT

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