Is this data less than ideal to view in powerpivot?

I have data in the format shown in the diagram below. It came from a rough and ready spreadsheet and i had to compile a quick and dirty report. Simple enough after importing into SQL Server. 

But I also tried with powerpivot and had a lot of issues and am wondering if this data is not ideal for powerpivot.

Issues:

- when i dragged on Applicant Name from the Applicant Table & qualification Name from the qualifications table, each applicant had ALL the qualifications under them, not just their qualifications. I fixed this by creating a dummy measure on the qualification table, but this seems like a terrible workaround.

-when i dragged on the Score_PartA, the sum was not correct. It summed up the value based on all the qualifications. e.g. if the applicant scored 5 on partA, and the applicant had 5 qualifications, the measure showed 25. i got around this by selecting the MAX, but again, this feels hacky.

Id appreciate any feedback on how i could approach this better.

August 28th, 2015 2:38pm

Hi,

for second issue I think you have relationship between Applicants and QuestonareResults in wrong direction. In your PowerPivot model.

for first issue I am not sure why is that but that is how it works. You have to have some measure.


  • Edited by Steelleg4 11 hours 39 minutes ago
Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 3:27pm

1) The MDX (yes, all Excel pivots generate MDX - this is translated by the SSAS instance (yes, Power Pivot runs a private instance of SSAS to support the Tabular model you create in Power Pivot) into DAX to return the appropriate data) that Excel generates in a pivot table uses a cross-join for any fields dragged onto the rows/columns. This is an artifact of the aggregation (note pivot tables are strictly an aggregation tool, not a query tool; while it is possible (and common) to use a pivot table as a poor-man's query builder, the results are not always reasonable, as you've discovered, and this use case is therefore not recommended) tool you have used as a front end to your Tabular model, and not an artifact of the relational model in the Tabular engine. The MDX generated is wrapped by a function that removes rows where the measure evaluates to a null value, but this only kicks in when a measure is included, as you discovered.

2) As Steelleg noted, relationships in a Tabular model are (currently) limited to uni-directional relationships. Context only propagates from the lookup, not from the looking-up table.
Without further insight into your model (sample data, or even what your intention/desired outcome is), it is very difficult to provide assistance.
August 28th, 2015 4:14pm

Both answers have been very helpful, thank you.

Relationship between Applicant and questionareResults is correct. In my data its actually 1 to 1, but in theory you could have many questionare results for each Applicant.

Im trying to view the data in a very standard tabular way

Applicant Name, Score_PartA, Score_PartB, QualificationName, QualificationYear

The reason i tried a pivot table is due to the fact that there are multiple qualifications per applicant. I wanted to allow the user to see a table with just one row for each applicant and their scores, but be able to drill down to view the qualifications.

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 4:22pm

Hi,

for second issue I think you have relationship between Applicants and QuestonareResults in wrong direction. In your PowerPivot model.

for first issue I am not sure why is that but that is how it works. You have to have some measure.


  • Edited by Steelleg4 Friday, August 28, 2015 7:29 PM
August 28th, 2015 7:26pm

Hi,

what I was trying to say is that you should double check your relationships in PowerPivot because in one-to-one scenario PowerPivot can create relationship with either direction and you dont have to notice it.

  • Edited by Steelleg4 23 hours 26 minutes ago
Free Windows Admin Tool Kit Click here and download it now
August 29th, 2015 3:41am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics