Measure filtering correctly my slicer but having trouble to sum

[nyann4]

Hello,
I'm trying to create a measure that will filter a slicer depending on others measures :

That measure "filter client" will check the value of CA_filter different than 0 and on my value slicer
I have another measure that will count the number of "1" from "filter_client" : 

As you can see as result when I'm displaying all of my values on a matrix : 

I have "1" for this 2 clients, and my "CA_filter" measure is also different than 0.
But the count_unique is does not equal "1" in both case.
Both client are displayed but only one is count, that happend in many case but not all the time.

And I can't figure it out since 2 days ^^"
Hope I can find help here !

[danextian · Accepted solution]

Hi @nyann4 

Measures don't have a row context so adding them to visuals that don't have such like a card may return an incorrect result. If it needs to be evaluated for each distinct client id before being summed up, use SUMX. Try @v-karpurapud 's solution.

[nyann4]

Thank's for replying, actually I doesnt have the time to specify it, but I already try a fews methods before posting here. Including a sumx version of my measure.

But I still figure out, trying your method, that the problem was hiding in my data. I was displaying "raison sociale" (name of the company) in my slicer, but using "code client" in my measure. 
I find out that some of the client has multiple code client, so in my matrix, there was an hiding agregation by "raison sociale", that showed a "1" but taking all of the "code client" of each of the "raison sociale" doesnt give the same result.
Anyway my measure is working now, thank you !

[v-karpurapud]

Hi @nyann4 

Welcome to the Microsoft Fabric Community Forum.
 

Regarding your issue with the measure, the filter_client measure checks if a client meets certain conditions and returns 1 or 0. While the matrix shows filter_client = 1 for several clients, the count_unique measure sometimes returns a lower count than expected. This happens because DAX measures like filter_client don't always evaluate row by row within a FILTER() function unless set up to do so.

To fix this, try replacing the count_unique measure with one that uses SUMX to go through each unique client and correctly evaluate the [filter_client] measure in the right row context.

Here’s an example:

count_unique =

SUMX (

    VALUES ( 'client_unique'[Code Client] ),

    IF ( [filter_client] = 1, 1, 0 )

)

If this helps, please mark the response as Accepted Solution so it can assist others. Giving Kudos is also appreciated if you found it helpful.

Thank you for being part of Fabric Community Forum.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[rajendraongole1]

Hi @nyann4  - If your data model allows it, convert filter_client to a calculated column instead of a measure. 

eg: as per snapshot

filter_client_column =
IF (
[Client_Unique (control)] > 'Jours de retard'[Jours de retard value],
IF ( [CA_Filter] <> 0, 1, 0 ),
0
)

call the above filter client column in your measure as 'client_unique'[filter_client_column] = 1

suggest , Always prefer columns for filtering logic instead of measure.

Hope this helps.