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
dmvega
Frequent Visitor

Help using CALCULATE and FILTER

Hello

 

I am trying to use a distinct count using CALCULATE and FILTER but for some reason is not working:

Measure "$ Net Sales" is = Sales - Return and is working fine, but when I try to count customers (SC_CODIGO) which "$ Net Sales" is > 0 it counts customers which Sales is > 0, not net sales.

 

dmvega_0-1637604710207.png

 

Thank you in advanced for your help!

 

1 ACCEPTED SOLUTION

Hmm. That's strange unless [Return] and [Sales] are measures too. (I was assuming they were columns.)

 

If they are measures, then I'd recommend a different approach:

#C Net =
SUMX (
    DISTINCT ( 'Venta Neta'[SC_CODIGO] ),
    IF ( [Return] > [Sales], 1 )
)

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

You have to be careful using measures inside of a FILTER since the measure is evaluated after a row context to filter context transition.

 

I'd recommend avoiding it where you can and instead write:

#C Net =
CALCULATE (
    DISTINCTCOUNT ( 'Venta Neta'[SC_CODIGO] ),
    FILTER ( 'Venta Neta', 'Venta Neta'[Return] > 'Venta Neta'[Sales] )
)

Thank you Alexis for your help, now "#C Neto" column is ok, but the total number is counting all rows, it shoud be 669 (which have 1 in #C Neto column) and not 749.

 

dmvega_1-1637606662764.png

 

Regards

Hmm. That's strange unless [Return] and [Sales] are measures too. (I was assuming they were columns.)

 

If they are measures, then I'd recommend a different approach:

#C Net =
SUMX (
    DISTINCT ( 'Venta Neta'[SC_CODIGO] ),
    IF ( [Return] > [Sales], 1 )
)

Thank you very much for your help! now is working!


Regards

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.