Single Measure to populate Multiple Rows

[N823198]

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(*)	ReceivedDate	AgeNumber	AGE_GROUP	Spreadsheet	Bucket 1	Bucket 2	Bucket 3	Bucket 4	Bucket 5	Bucket 6	Age Number	AGEGRP	AGE#
0		5/15/2021			PCS_CVTY[2]	0	0	0	0	0	0
0	1	5/15/2021	6	> than 90 days	PCS_CVTY [2]	0	0	0	0	0	1			6
0	3	5/15/2021	2	>= 2 days < 15 days	PCS_CVTY[2]	0	-2	0	0	0	0			2
0	1	5/15/2021	1	< than 2 days	PCS_CVTY [2]	0	0	0	0	0	0			1
0	3	5/15/2021	3	>= 15 days < 30 days	PCS_CVTY [2]	0	0	2	0	0	0			3
0	4	5/15/2021	4	>= 30 days < 60 days	PCS_CVTY [2]	0	0	0	4	0	0			4
1		5/15/2021			PCS [7]	0	0	1	0	0	0	3	>= 15 days < 30 days	3
4		5/15/2021			PCS [7]	0	4	0	0	0	0	2	>= 2 days < 15 days	2
6		5/15/2021			PCS [7]	6	0	0	0	0	0	1	< than 2 days	1
0	1	5/15/2021	6	> than 90 days	PCS [2]	0	0	0	0	0	1			6
0	3	5/15/2021	2	>= 2 days < 15 days	PCS [2]	0	3	0	0	0	0			2
0	1	5/15/2021	1	< than 2 days	PCS [2]	1	0	0	0	0	0			1
0	3	5/15/2021	3	>= 15 days < 30 days	PCS [2]	0	0	3	0	0	0			3
0	4	5/15/2021	4	>= 30 days < 60 days	PCS [2]	0	0	0	4	0	0			4
0		5/15/2021			PCS [2]	0	0	0	0	0	0
0		5/15/2021			PCS [7]	0	0	0	0	0	0

[N823198 · Accepted solution]

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

[N823198]

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

[N823198]

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

[selimovd]

HEy @N823198 ,

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

[N823198]

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.