Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Single Measure to populate Multiple Rows

Created a single measure to populate rows based on aging (Buckets 1-6) and spreadsheet ([2], [7] and CVTY [7]).  Aging bucket counts for [2] is based on Received Date, AgeNumber and COUNT(*) Aging...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Closing this topic as I figured out an extensive workaround to get the desired values to populate the matrix.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

The bucket columns are calculated columns, NOT a measure. The AGE#, AgeNumber and Age Number changes for each bucket column calc. I've whittled down my Bucket 1 - 6 DAX to...

 

IF('Scorecards Merged data'[AGE#] = 2 && 'Scorecards Merged data'[Age Number] = 2 && CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"), 'Scorecards Merged data'[COUNT(TOPICID)], IF('Scorecard Merged data'[Age#] <> 2, 0, IF('Scorecards Merged data'[AGE#] = 2 && 'Scorecards Merged data'[AgeNumber] = 2 && CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[2]") && NOT(CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"CVTY")), 'Scorecards Merged data'[COUNT(*)], [Calc CVTY]))

 

There are three vendors each having the same three spreadsheets. For each vendor, the [COUNT(TOPICID)]...[7] and [COUNT(*)]...[2] counts are correct,  the difference between the two is where I'm failing. I've added the measure [Calc CVTY] to the calculation and it works as I need it to work for the difference, but only for "VendorA" and Bucket 2 as circular dependency occurs for the other buckets. Can the [Calc CVTY] measure be transformed to work for each bucket...or what DAX calculation can I add to correctly calculate the difference for all six buckets for each Vendor.

 

Calc CVTY = 

IF(SUM('Scorecards Merged data'[COUNT(*)]) <= CALCULATE(SUM('Scorecards Merged data'[COUNT(TOPICID)]), ALLEXCEPT('Scorecards Merged data', 'Scorecards Merged data'[ReceivedDate], 'Scorecards Merged data'[AGE#], 'Scorecards Merged data'[Vendor], 'Scorecards Merged data'[Age Number]), CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"), 'Scorecards Merged data'[Age Number] = 2, 'Scorecards Merged data'[AGE#] = 2, 'Scorecards Merged data'[Vendor] = "VendorA"), 0, SUM('Scorecards Merged data'[COUNT(*)]) - CALCULATE(SUM('Scorecards Merged data'[COUNT(TOPICID)]), ALLEXCEPT('Scorecards Merged data', 'Scorecards Merged data'[ReceivedDate], 'Scorecards Merged data'[AGE#], 'Scorecards Merged data'[Vendor], 'Scorecards Merged data'[Age Number]), CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"), 'Scorecards Merged data'[Age Number] = 2, 'Scorecards Merged data'[AGE#] = 2, 'Scorecards Merged data'[Vendor] = "VendorA")