Forum Discussion
Concatenate rows values based on DAX MEASURE value
I am trying to concatenate the [Step] column and also sum the [Quantity] column if the cumulative total is < required balance AND also the first instance where the cumulative total is >= required balance
What I am trying to do is to find out for each specific product, are the ones which has progressed the furthest (highest [Step]) along the chain, and seek them out to meet my [Required balance].
The pain comes because [Product], [Step], [Quantity], [Required balance] is part of an actual column, while [Cumulative total] and [Cumulative total < Required balance] is a calculated measure.
2 tables are involved here:
Required balance: [Product], [Required balance]
Quantity: [Product], [Step], [Quantity]
'Required balance'[Product] is linked to 'Quantity'[Product] in a one-to-many relationship.
| Product | Step | Quantity | Required balance | Cumulative total | Cumulative total < Required balance |
| A | 10 | 12 | 75 | 12 | True |
| A | 9 | 71 | 75 | 83 | False |
| A | 8 | 12 | 75 | 95 | False |
| A | 7 | 48 | 75 | 143 | False |
Ideally my output should be:
| Product | Steps required | Cumulative total |
| A | 10, 9 | 83 |
Hi Anonymous
Create the following measures:
Quantity Value = sum(Quantity[Quantity]) Steps Below = VAR QuantityCumulative = ADDCOLUMNS ( ALLSELECTED ( Quantity[Step] ), "@Quantity", [Quantity Value], "@CumulativeQuantity", [CUMULATIVE TOTAL] ) VAR QuantityBelowRequired = COUNTROWS ( FILTER ( QuantityCumulative, [@CumulativeQuantity] < SUM ( 'Required Balance'[Required balance] ) ) ) + 1 RETURN CONCATENATEX ( TOPN ( QuantityBelowRequired, QuantityCumulative, [@CumulativeQuantity], ASC ), Quantity[Step], ",", Quantity[Step], DESC ) Cumulative value = VAR QuantityCumulative = ADDCOLUMNS ( ALLSELECTED ( Quantity[Step] ), "@Quantity", [Quantity Value], "@CumulativeQuantity", [CUMULATIVE TOTAL] ) VAR QuantityBelowRequired = COUNTROWS ( FILTER ( QuantityCumulative, [@CumulativeQuantity] < SUM ( 'Required Balance'[Required balance] ) ) ) + 1 RETURN MAXX ( TOPN ( QuantityBelowRequired, QuantityCumulative, [@CumulativeQuantity], ASC ), [@CumulativeQuantity] )Final result below and in attach PBIX file.
2 Replies
Hi Anonymous
Create the following measures:
Quantity Value = sum(Quantity[Quantity]) Steps Below = VAR QuantityCumulative = ADDCOLUMNS ( ALLSELECTED ( Quantity[Step] ), "@Quantity", [Quantity Value], "@CumulativeQuantity", [CUMULATIVE TOTAL] ) VAR QuantityBelowRequired = COUNTROWS ( FILTER ( QuantityCumulative, [@CumulativeQuantity] < SUM ( 'Required Balance'[Required balance] ) ) ) + 1 RETURN CONCATENATEX ( TOPN ( QuantityBelowRequired, QuantityCumulative, [@CumulativeQuantity], ASC ), Quantity[Step], ",", Quantity[Step], DESC ) Cumulative value = VAR QuantityCumulative = ADDCOLUMNS ( ALLSELECTED ( Quantity[Step] ), "@Quantity", [Quantity Value], "@CumulativeQuantity", [CUMULATIVE TOTAL] ) VAR QuantityBelowRequired = COUNTROWS ( FILTER ( QuantityCumulative, [@CumulativeQuantity] < SUM ( 'Required Balance'[Required balance] ) ) ) + 1 RETURN MAXX ( TOPN ( QuantityBelowRequired, QuantityCumulative, [@CumulativeQuantity], ASC ), [@CumulativeQuantity] )Final result below and in attach PBIX file.