Variant of measure returns different value

[Dunner2020]

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

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 any one help me about this behaviour?

Sample file can be download from here

[PhilipTreacy · Accepted solution]

Hi @Dunner2020 

Please @ mention me in your replies or I won't see them.  Type @ then select my name.

What are you actually trying to achieve? 

As I've said, the 2nd measure where you are declaring variables and then trying to get the MAX of text columns will return unexpected results.  Basically that won't work so don't use that approach.

Your first measure is syntactically correct and will return sensible results.  Do you find there is there something wrong with the results it is giving you?

Unique_count = CALCULATE(
                            DISTINCTCOUNT('Table'[Event_date]),
                            FILTER('Table','Table'[Notified_event]="TRUE" || 
                                    ('Table'[Event_type]="3" && 'Table'[Status]="1")
                            )
)

But you are storing numeric values as text in both the Event_type and Status columns.  Why not use these columns as numeric ?

Regards

Phil

[PhilipTreacy]

Hi @Dunner2020 

In your 2nd version of the measure, you are getting the MAX of 2 text columns 'Table'[Notified Event] and 'Table'[Event_status].  Getting MAX for text columns can return unexpected results.

In your RETURN statement you are then saying FILTER the Table where _notified ="Yes" but you've already worked out a value for _notified.  Saying filter this table where a defined value = another value will result in a boolean True or False.  Same for _Status = "Completed".  I'd expect unexpected behaviour due to this.

If you can supply some real data maybe I can come up with a working solution for you.

regards

Phil

[Dunner2020]

Thanks for the reply. I have added sample file in the post.