DistinctCount with fitlers help

[traviscole]

I'm not sure what I am doing wrong here, so hoping someone can point out what my mistake is.

!! test1 = CALCULATE(

DISTINCTCOUNT(Store[State]),

FILTER(Store, [!! test2]>1))

In my mind, this should be returning a DistinctCount for states where the test2 value is >1.  (2 in my case)

What I am getting is a DistinctCount for states where the value is not >1.  (48 in my case)

I've played around and can't figure out why it's returning the opposite of what it should be.

I can get around it and just make a new measure where it's   New Measure = 50 - test1    to get the 2 value that is supposed to be returned, but I'm just curious to see if anyone can tell me why the orignal test1 measure is being odd.

[traviscole]

Update:  Made a dummy file and everything works as it should.  In the real file, still getting the opposite result when using one ratio, and completely wrong results when using another ratio.  Will attmept to replicate the problem in the dummy file and upload it.

Update again:  Well, it all works perfectly in the dummy file.  I think my problem is something where the values the ratio is being calculated on is a SUM in the dummy file, and in the real file that I can't really clean up and share it is being based on a SUMX using a var and filtering.  So at least I know where the problem probably is, now I just need to try to fix it.

Last update today:  I got nothing and am headed off on vacation.  When I attempt to filter the state count based on the ratios, I get the opposite results for one ratio, results that make no sense or are the max number for other ratios.  I'll see what ideas people have when I get back next week, wish I could replicate the problem in a dummy data set, but I am failing at that.  

Alternative solution idea if anyone has any ideas:  When I make the state list as a table and filter it down on the visual filter pane, I can narrow it to the states I need.  But when I change that table into a card and the state measuer into distinct count, it shows that it is counting all states (50), even though the filter is still there on the visual pane to filter out values below a certain number.

[v-rzhou-msft]

Hi @traviscole 

Your measure logic is correct. Measure will show you aggregations. I think incorrect return should be caused by 

[!! test2] Measure.

!! test1 = CALCULATE(
DISTINCTCOUNT(Store[State]),
FILTER(Store, [!! test2]>1))

Due to I don't know your data model, I will give you some advice. You can build a table visual by Store[State] and [!! test2] measure. Then you can check whether [!! test2] measure returnd correct result. In your table visual, there should be 2 State>1 and others should all less than or equal to 1.

If this reply still couldn't help you solve your problem, please reproduce your issue and share a sample file with me.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[traviscole]

Yeah, unfortunately everything works perfect in my dummy file, I can't replicate the errors.  🤣

The !test1 where it should be putting th state count when put into table for is just being crazy, returning a 1 for states above and below 1, while returning nothing for states above and below 1.  So I have no clue.

I'm shelving the idea for now, it's not a big deal, thanks for the attempts to help.

[v-rzhou-msft]

Hi @traviscole 

Currently, both of us couldn't find reason in new sample file. Your dax code is correct. There may be something difference between new and old one.

I think you can troubleshoot by checking your relationships, data source or connection mode in your old file.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.