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 bucket counts for [7] is based on Received Date, Age Number and COUNT(TOPICID)
  • Aging bucket counts for CVTY [2] is based on Received date and...
    • if no aging for [2] then 0
    • if bucket count for [2] is <= bucket count for [7] then 0
    • otherwise, bucket count [2] minus bucket count for [7]

Measure = IF('Scorecards Merged data'[AGE#] = 2,IF('Scorecards Merged data'[AgeNumber] = 2 && 'Scorecards Merged data'[AGE#] = 2 && NOT(CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"CVTY")), 'Scorecards Merged data'[COUNT(*)], IF('Scorecards Merged data'[AGE#] = 2 && 'Scorecards Merged data'[Age Number] = 2 && CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"), 'Scorecards Merged data'[COUNT(TOPICID)], IF(CALCULATE(SUM('Scorecards Merged data'[COUNT(*)]),ALLEXCEPT('Scorecards Merged data','Scorecards Merged data'[ReceivedDate],'Scorecards Merged data'[AGE#], 'Scorecards Merged data'[AgeNumber]),CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[2]"),'Scorecards Merged data'[AgeNumber] = 2, 'Scorecards Merged data'[AGE#] = 2) <= CALCULATE(SUM('Scorecards Merged data'[COUNT(TOPICID)]),ALLEXCEPT('Scorecards Merged data','Scorecards Merged data'[ReceivedDate],'Scorecards Merged data'[AGE#], 'Scorecards Merged data'[Age Number]),CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"),'Scorecards Merged data'[Age Number] = 2, 'Scorecards Merged data'[AGE#] = 2), 0, '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'[Age Number]),CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"),'Scorecards Merged data'[Age Number] = 2, 'Scorecards Merged data'[AGE#] = 2)))),0)

 

Output Power BI Bucket 1-6 columns are correct for [2] and [7], but for CVTY [2], diffrence is sometimes correct and sometimes incorrect...not sure where my disconnect is within the measure.

 

COUNT(TOPICID)COUNT(*)ReceivedDateAgeNumberAGE_GROUPSpreadsheetBucket 1Bucket 2Bucket 3Bucket 4Bucket 5Bucket 6Age NumberAGEGRPAGE#
0 5/15/2021  PCS_CVTY[2] 000000   
015/15/20216> than 90 daysPCS_CVTY [2] 000001  6
035/15/20212>= 2 days < 15 daysPCS_CVTY[2] 0-20000  2
015/15/20211< than 2 daysPCS_CVTY [2] 000000  1
035/15/20213>= 15 days < 30 daysPCS_CVTY [2] 002000  3
045/15/20214>= 30 days < 60 daysPCS_CVTY [2] 000400  4
1 5/15/2021  PCS [7] 0010003>= 15 days < 30 days3
4 5/15/2021  PCS [7] 0400002>= 2 days < 15 days2
6 5/15/2021  PCS [7] 6000001< than 2 days1
015/15/20216> than 90 daysPCS [2] 000001  6
035/15/20212>= 2 days < 15 daysPCS [2] 030000  2
015/15/20211< than 2 daysPCS [2] 100000  1
035/15/20213>= 15 days < 30 daysPCS [2] 003000  3
045/15/20214>= 30 days < 60 daysPCS [2] 000400  4
0 5/15/2021  PCS [2] 000000   
0 5/15/2021  PCS [7] 000000   

 

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.

5 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    HEy Anonymous ,

     

    do yourself a favor and use SWITCH(TRUE()) and definitely use the DAX Formatter. Then it should be possible to understand the measure.

     

    Then next question, you posted one measure and refer to 6 columns. Is that measure used 6 times? Are they calculated column? How does the measure change between the 6 usages?

    Make it as easy as possible, then people can help you.

     

    Best regards

    Denis

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I've yet to use the Switch(True()) or DAX Formatter so I'll need to read up on them.

       

      Yes, the measure is used 6 times for each calculated column with the only change being the AGE#, AgeNumber and Age Number for reference to the "bucket". Original example above is the measure used for Bucket 2 column.  The measure used for Bucket 3 column is exactly the same as the measure for Bucket 2 column the exception of the AGE#, AgeNumber and Age Number in the measure being "3"...and so on for the other Bucket column measures.

  • 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")

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    The calculation is NOT a measure, but a calculated column. The following DAX is working for a singluar vendor (there are three), for each of the six Bucket columns, but how do I change the DAX to look at each vendor as applicable within each column

     

    Bucket 2 = IF('Scorecards Merged data'[AGE#] = 2 && 'Scorecards Merged data'[Age Number] = 2 && CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"), 'Scorecards Merged data'[COUNT(TOPICID)], IF('Scorecards Merged data'[AGE#] <> 2, 0, IF('Scorecards Merged data'[AGE#] = 2 && 'Scorecards Merged data'[AgeNumber] = 2 && NOT(CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"CVTY")) && CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[2]"), 'Scorecards Merged data'[COUNT(*)], IF('Scorecards Merged data'[AgeNumber] = 2 && CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"CVTY") && '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'[Vendor] = "Vendor1", 'Scorecards Merged data'[AGE#] = 2) <=0, 0, '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'[Vendor] = "Vendor1", 'Scorecards Merged data'[AGE#] = 2)))))

     

    Matrix table...

    Date

    Vendor

    Bucket 1

    Bucket 2

    Bucket 3

    Bucket 4

    Bucket 5

    Bucket 6

    5/22/2021

    Vendor2_Special

    0

    0

    0

    0

    0

    0

    5/22/2021

    Vendor2[2]

    0

    5

    5

    4

    0

    1

    5/22/2021

    Vendor2[CVTY]

    0

    3

    5

    4

    0

    1

    5/22/2021

    Vendor2[7]

    7

    5

    2

    0

    0

    0

    5/22/2021

    Vendor3[2]

    0

    1

    0

    1

    1

    2

    5/22/2021

    Vendor3[CVTY]

    0

    0

    0

    1

    1

    2

    5/22/2021

    Vendor3_Special

    0

    0

    0

    0

    0

    0

    5/22/2021

    Vendor3[7]

    0

    0

    0

    0

    0

    0

    5/22/2021

    Vendor1[2]

    0

    1

    1

    0

    0

    0

    5/22/2021

    Vendor1[CVTY]

    0

    0

    1

    0

    0

    0

    5/22/2021

    Vendor1_Special

    0

    0

    0

    0

    0

    0

    5/22/2021

    Vendor1[7]

    0

    2

    0

    0

    0

    0

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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