Strange behavior in week over week table

[tessaw]

I’m running into an issue that I cannot find a reasonable explanation for. I have a dashboard that calculates the total number of calls our service has by the response code for that call. We are interested in seeing this break down in a week over week view.

In the screenshot below (with data hidden), I show two views of the same dashboard with different times selected. 4/13 – 4/19 is the time frame I'm using in this example. I would expect that the results in the table should be the exact same whether 4/13 – 4/19 is the “newer” time frame or the “older” time frame. This is not the case, but only for a few of the response code rows. For every row except 500, 412, and 403 (the response codes with the lowest counts), when 4/13 – 4/19 is the “older” time frame, many or all of the counts are missing. Every other row, including 200 with the highest counts, has the exact same value whether or not 4/13 – 4/19 is the newer or older time frame. Can anyone help explain why or point me to any good next steps for debugging?

Thanks in advance,

Tessa

Starting from the top:

Newer time frame slicer

• Selects a time range
• For week over week, we select a week’s worth of time
• Measures are created to capture the slicer values with

  TimeStart1 = CALCULATE(MIN(AzureDataExplorer[StartDate]), ALLSELECTED(AzureDataExplorer[StartDate]))

  For the start date. The same but with MAX for the end date. A number of days between the two dates is also saved into a measure.

Older time frame start label

TimeStart2 = [TimeStart1] - [TimeDif]

Older time frame end label

TimeEnd2 = [TimeStart1] - 1

Older Total Count column in table

From a new measure

XoXTotal2 =
CALCULATE (
MeasuresGroup[TotalCount],
DATESINPERIOD ( AzureDataExplorer[StartDate], [TimeStart1] - 1, [TimeDif]*-1, DAY )
)

Newer Total Count column in table

From a new measure

XoXTotal1 =
CALCULATE (
MeasuresGroup[TotalCount],
DATESINPERIOD ( AzureDataExplorer[StartDate], [TimeEnd1], [TimeDif]*-1, DAY )
)

[Icey]

Hi @tessaw ,

I need a dummy PBIX file for test. Please use virtual data instead of real data and remove sensitive information.

It is suggested to upload the file to OneDrive for Business and then paste the link here.

Best Regards,

Icey

[tessaw]

Hi @Icey - you can find a dummy pbix file here:
https://1drv.ms/u/s!AjUa2LpfGyWTgqdqq1ke0YJB-ViPCQ?e=K1DWpe
Thank you for the help

[Icey · Accepted solution]

Hi @tessaw ,

The cause is that your date filter will also filter the Response Code. Another separate Response Code table is needed to be created, which won't be filtered by your date filter. Steps are as follows:

1. Create a Response Code table.

Response Code = VALUES(AzureDataExplorer[ResponseCode])

2. Change your [TotalCount] measure like so:

TotalCount =
CALCULATE (
    SUM ( AzureDataExplorer[Count] ),
    FILTER (
        AzureDataExplorer,
        AzureDataExplorer[ResponseCode] IN VALUES ( 'Response Code'[ResponseCode] )
    )
)

3. Replace AzureDataExplorer[ResponseCode] with 'Response Code'[ResponseCode] in your table visuals.

 

Best Regards,

Icey

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

[tessaw]

Ahhh!! Thank you! That worked! 
I understand that the date filter was also filtering response code but could you explain why were only a few of the counts of response codes affected? Why wouldn't all the values be affected?

[Icey]

Hi @tessaw ,

This is because there are duplicate response codes in the two date ranges. If you look closely, you will find that the corresponding values in the two date ranges before and after part of the same response codes are different.

Best Regards,

Icey