Forum Discussion

andyL's avatar
andyL
New Member
3 years ago

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

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?

Need Help

1 Reply

  • Kishore_KVN's avatar
    Kishore_KVN
    Solution Sage
    3 years ago

    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!!