For some reason I cannot get this to work. I have been struggling with a many to many relationship in powerpivot. I've read the work of Russo, Ferrari, and Brueckl and tried to apply it to some success. Here is what I need to have displayed: Complaints about the Top N Companies over time. So, the user can select the top n companies by sales amount and only those companies will be listed, displaying their complaint counts per year/quarter/month, etc.
My tables:
Calendar: DateKey, year, etc.
Complaints: Date of Complaint, Complaint ID, CompanyID (duplicate values on Company ID and Complaint ID, as more than one company can be associated with a complaint)
UniqueCompanyList: CompanyID, CompanyName
CompanyRank: CompanyID, CompanyName, SalesAmount, Location (This has duplicate values for company ID due to Location)
Relationships are Calendar[DateKey]-Complaints[Day of Complaint]
Complaints[CompanyID]->UniqueCompanyList[Company ID]<- CompanyRank[CompanyID]
My measure for complaint count is: Complaint Count:=calculate(count(Complaints[ComplaintID]), filter(UniqueCompanyList, calculate(COUNTROWS(CompanyRank)>0)))
This measure correctly displays in a pivot when selecting CompanyRank[CompanyID] and my created measure. I can see the total number of complaints received by a companyHooray!
But, when I throw Date into the pivot, so that I can see the distribution of complaints over time I get nothing. I only can see the list of the complaint count for the company or the whole pivot list just rolls up and is blank. Please help!
My tables:
Calendar: DateKey, year, etc.
Complaints: Date of Complaint, Complaint ID, CompanyID (duplicate values on Company ID and Complaint ID, as more than one company can be associated with a complaint)
UniqueCompanyList: CompanyID, CompanyName
CompanyRank: CompanyID, CompanyName, SalesAmount, Location (This has duplicate values for company ID due to Location)
Relationships are Calendar[DateKey]-Complaints[Day of Complaint]
Complaints[CompanyID]->UniqueCompanyList[Company ID]<- CompanyRank[CompanyID]
My measure for complaint count is: Complaint Count:=calculate(count(Complaints[ComplaintID]), filter(UniqueCompanyList, calculate(COUNTROWS(CompanyRank)>0)))
This measure correctly displays in a pivot when selecting CompanyRank[CompanyID] and my created measure. I can see the total number of complaints received by a companyHooray!
But, when I throw Date into the pivot, so that I can see the distribution of complaints over time I get nothing. I only can see the list of the complaint count for the company or the whole pivot list just rolls up and is blank. Please help!
