Creating a difference measure where data may be in one data set but not the other

[andyL]

Hi,

I am trying to create a dashboard that shows the differnces between two sets of data at points in time.

I have the same report ran at two points in time Monday morning and Friday evening. Its helpdesk data so each call has a unique ID and a queue that it is allocated to.

At the moment I have the two reports linked on a many to many relationship on the queue name.

I am trying to create a measure to identify the differnces in the counts between the two datasets. I have used the following 

Difference = DISTINCTCOUNT('OpenCalls-SupportWorksFriday 5pm'[h_formattedcallref])-DISTINCTCOUNT('OpenCalls-SupportWorksMonday 8am - Copy'[h_formattedcallref])

On the whole this works well but I have found some a discrpeancy where if a queue has data on one of the reports but then does not have any data on the other I get nothing displayed. Ideally I would want to show 0 when there is no data and then the count figure when there is a record.  Obviously if there is no record in either data set it does not need to be displayed. 

Can anyone offer any advice on how his might be done?

[Kishore_KVN]

Hey @andyL 

1. Use DISTINCTCOUNTNOBLANK or apply IF condition for excluding blanks before the formula that you have written which helps you to ignore if records are not there in both the datasets. 

2. At the end of your measure, write "+0" to get results as '0' where ever its blank.

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