Variant of same measure produces different result

DAX DAX Calculations
1

Hi there,

I am calculating a distinctcount using filter statement as follow:

Unique count = Calculate(Distinctcount(Event_date), filter(Table, Table[Notified_event]=“TRUE” || (Table[‘Event_type’]=3 && Table[‘Event_status’]=“1”)))

Table[‘Event_type’] has three values i.e. 1,2, and 3. When Table[Notified_event] has value “TRUE” then it means that Event_type value is equal to 2.

The above measure returns some number. However, when I changed the above measure into different way as shown below:

Variant Unique count =

Var _notified = Max(Table, Table[Notified_event])

Var _Type = MAx(Table[‘Event_type’])

Var _Status = Max(Table[‘Event_status’])

RETURN
Sample 4.pbix (69.2 KB)
Calculate(Distinctcount(Event_date), filter(Table,_notified =“TRUE” || (_Type=3 && _Status =“1”)))

When I ran the above variant, it returned a different number. When I debugged the [Variant Unique count], I found that it also returns rows with Event_type value = 1 which was not the case. I am not sure why is returning 1 value. Is it because I used MAX() function. Could anyone help me about this behavior? I have uploaded the sample file.

2

@leo_89 Variant version is incorrect, you are doing a Boolean operation between variables and constant but that doesn’t impacts the table returned by FILTER.

3

@AntrikshSharma Thanks for your reply. Sorry to bother you. I could not understand by Boolean operation between variables and constant. Could you please point out in which part of the formula I am making mistake?

4

@leo_89,

Given that it’s 2:30 AM in India, I’ll attempt to pinch-hit for @AntrikshSharma.

It’s the highlighted portions below giving you trouble.

image

Keep in mind that cards do not have any inherent evaluation context. Therefore, in a card MAX( Table[Notified_event] ) is always going to evaluate to TRUE (the max value of the entire column). Thus, in your filter condition you are saying to filter based on TRUE = TRUE OR (some other condition). Given that TRUE will always be equal to TRUE, you never get to the second condition (which by the way would have the same problem if you did get to it), and thus the measure always returns the value of DISTINCTCOUNT(Table[Event Date] ) over the entire (i.e., unfiltered) table = 1463.

I actually just this week did a video on fixing measure results in cards that discusses this evaluation context issue at length.

I hope this is helpful.

  • Brian
3 Likes