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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.