topic Using filter functions on a measure in table in DAX Commands and Tips

Using filter functions on a measure in table

abeirne — Tue, 10 May 2022 17:20:17 GMT

Hi all, I am trying to show the email capture rate of my stores, but the table I am currently using shows all of the employees. I would like to show the email capture rate per store versus per employee. I am thinking maybe an ALL() would work? I have tried all() on the main store # table and columns but did not seem to work.

Current measure:

Email Capture % =

CALCULATE(

DISTINCTCOUNT(Invoice_[Customer_Email_Address]) / 'Invoice_Detail_'[GC],

ALL(Invoice_)

)

So for store 17, I want the email capture rate to be the same, showing the capture rate for the whole store instead of individually, and so on for the next stores. I have redacted some info. Thank you all for the help.

Re: Using filter functions on a measure in table

amitchandak — Wed, 11 May 2022 06:14:24 GMT

abeirne , Not very clear. Try like

Current measure:
Email Capture % =
CALCULATE(
divide(DISTINCTCOUNT(Invoice_[Customer_Email_Address]) , 'Invoice_Detail_'[GC]),
ALLEXCEPT(Invoice_, Invoice_ [Store])
)

Re: Using filter functions on a measure in table

abeirne — Wed, 11 May 2022 13:34:59 GMT

Yeah I know it was not super clear, sorry for that. I hadn't thought of the allexcept, I will try it. Thank you!

Re: Using filter functions on a measure in table

Anonymous — Fri, 13 May 2022 09:49:08 GMT

Hi abeirne ,

Are 'Invoice_Detail_' [GC] the same for the same stores?

Please try this formula.

Email Capture % = CALCULATE ( DISTINCTCOUNT ( Invoice_[Customer_Email_Address] ), FILTER ( ALL ( Invoice_ ), [stores] = SELECTEDVALUE ( stores ) ) ) / SELECTEDVALUE ( 'Invoice_Detail_'[GC] )

Best Regards,

Jay