Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
abeirne
Helper II
Helper II

Using filter functions on a measure in table

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

abeirne_0-1652198166874.png

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. 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
abeirne
Helper II
Helper II

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

amitchandak
Super User
Super User

@abeirne , Not very clear. Try like

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.