I am working on a PowerPivot report that has the following tables/relationships:
FactTable:
- Client
- Issue
- DateID
DimDate
- DateID
- Year
- Quarter
- Month
I wish to create a calculated measure that is COUNT(Issue)/DISTINCT(Client) by Year and Quarter and that is filtered to exclude clients who only had 1 issue per quarter from the calculation.
The first part of this is straightforward enough by creating two calculated measures: [Distinct Client] = COUNTROWS(DISTINCT('FactTable'[Client])) [Issues per Client] = 'FactTable'[Count of Issue]/'FactTable'[Distinct Client]
Pivot: Row Labels = Year, Quarter Values = [Issues per Client]
I'm just learning DAX so I'm not sure how to put go about this. It seems that CALCULATE() or CALCULATETABLE() are my best bets, but I don't know how to use it to filter on [Issues per Client per Quarter]. Any help would be appreciated!