Search code examples
excelpowerbidaxpowerpivotmeasure

PowerPivot Relationships showing blank rows of data and not calculating the measures correctly

I have 3 different data feeds going into PowerPivot as per below: Quality Scoring Data, Learning Scoring, Data Call Handling Data

To get all three of these to pull through into one pivot table with the correct data on each agent I have 3 other tables that also are in PowerPivot: Dates (to match all the different data tables up to the right dates), Employee Data (to match the employee numbers up to the different data), Tenure Banding (so I can correctly categorise each agent into the right tenure brackets)

The relationships between each table is than able to be formed.

All worked great. I have multiple measures on the Call Handling Data to calculate the AHT, Sickness, ACW, Utilisation which works with no issues, until I created one measure in particular.

I am trying to create a metric if an agents AHT is over a specific time then it would display 1, if it doesn't then it would be 0 as this will then create a score on how many metrics they have passed. Once I have managed to do it for AHT I will be doing it for the others so we have a score across 5 metrics on how many they have passed.

I have created the following measure in PoverPivot: AHTPASS:=IF(AND(SUM([IB_THT])/SUM([IB_CALLS])<=340,SUM([IB_THT])/SUM([IB_CALLS])<>0),1,"")

This does create the right outcome but my issue is all of a sudden Every employee is now displayed in the data table, even employees that have no data for any of the other stats. Agents which have left many months ago etc. Even when the table is filtered to the current month.

All my other measures I have created haven't done this so im not sure why this particular formula is causing the issue.

It seems fine for the AHT metric and other call handling that I have done: IB_AHT:=IFERROR((SUM([IB_THT])/(SUM([IB_CALLS]))),0)

TO make it easier to understand here is a screen shot of the pivot before I add in AHT pass or failed column: WORKING FINE SCREENSHOT

After adding in the AHT pass or fail measure: AFTER ADDING IN THE AHT PASS OR FAIL HELPER COLUMN

I cannot work out why all the other measures are working fine and not doing this, yet the IF formula in my measure seems to bring through every agent from the agent list even if they have no data in any of the other tables.

I hope this makes sense and someone can give me some guidance on the best way to achieve the results.

Solution

  • That's just the way DAX works. Normally, blanks are pruned but you are returning a value even when it would normally be blank. Try something like this:

    AHTPASS:=

IF(AND(SUM([IB_THT])/SUM([IB_CALLS])<=340,SUM([IB_THT])/SUM([IB_CALLS])<>0),1,BLANK())