In DAX, can you loop through each value from a column variable in a SUMMARIZE table?

[minnawu]

alerts =  

VAR EndOfWeekColumn = DISTINCT('DATE'[EndOfWeek]) 

RETURN
UNIONX(
SUMMARIZE(TABLE, Column1, Column2, etc, 
"New Column1", CALCULATE(...using the next value in EndOfWeekColumn...),
"New Column2", CALCULATE(...using the next value in EndOfWeekColumn...))
)

[nizebelami]

I also need a solution, I have the same problem

[minnawu]

@v-chuncz-msft Sorry, but each of those 3 solutions you linked only go through the table once whereas I need to loop through the table as many times as there are values in my column variable. Is there another way (perhaps not DAX)?

I need to shrink the below into something more condensed if possible:

alerts = 
UNION(SUMMARIZE(FILTER(reviews_all,[eRetailer]="amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,11,19),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,19) -30),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,19) -60, 'reviews_all'[review_timestamp] < date(2019,11,19) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,19) -30) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,19) -60, 'reviews_all'[review_timestamp] < date(2019,11,19) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,11,19) -30),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,11,19) -30),
"URL of recent reviews", CONCATENATE("https://amazon.ca/product-reviews/", CONCATENATE(CALCULATE(FIRSTNONBLANK('reviews_all'[SKU],'reviews_all'[SKU]), 'reviews_all'[review_rating] <= 3), "/ref=cm_cr_arp_d_viewopt_sr?sortBy=recent&pageNumber=1"))),

SUMMARIZE(FILTER(reviews_all,[eRetailer]="amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,11,12),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,12) -30),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,12) -60, 'reviews_all'[review_timestamp] < date(2019,11,12) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,12) -30) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,12) -60, 'reviews_all'[review_timestamp] < date(2019,11,12) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,11,12) -30),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,11,12) -30),
"URL of recent reviews", CONCATENATE("https://amazon.ca/product-reviews/", CONCATENATE(CALCULATE(FIRSTNONBLANK('reviews_all'[SKU],'reviews_all'[SKU]), 'reviews_all'[review_rating] <= 3), "/ref=cm_cr_arp_d_viewopt_sr?sortBy=recent&pageNumber=1"))),

SUMMARIZE(FILTER(reviews_all,[eRetailer]<>"amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,11,12),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,12) -30),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,12) -60, 'reviews_all'[review_timestamp] < date(2019,11,12) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,12) -30) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,12) -60, 'reviews_all'[review_timestamp] < date(2019,11,12) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,11,12) -30),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,11,12) -30),
"URL of recent reviews", ""),


SUMMARIZE(FILTER(reviews_all,[eRetailer]="amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,11,5),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,5) -30),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,5) -60, 'reviews_all'[review_timestamp] < date(2019,11,5) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,5) -30) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,5) -60, 'reviews_all'[review_timestamp] < date(2019,11,5) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,11,5) -30),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,11,5) -30),
"URL of recent reviews", CONCATENATE("https://amazon.ca/product-reviews/", CONCATENATE(CALCULATE(FIRSTNONBLANK('reviews_all'[SKU],'reviews_all'[SKU]), 'reviews_all'[review_rating] <= 3), "/ref=cm_cr_arp_d_viewopt_sr?sortBy=recent&pageNumber=1"))),

SUMMARIZE(FILTER(reviews_all,[eRetailer]<>"amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,11,5),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,5) -30),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,5) -60, 'reviews_all'[review_timestamp] < date(2019,11,5) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,5) -30) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,11,5) -60, 'reviews_all'[review_timestamp] < date(2019,11,5) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,11,5) -30),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,11,5) -30),
"URL of recent reviews", ""),


SUMMARIZE(FILTER(reviews_all,[eRetailer]="amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,10,29),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,29) -30),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,29) -60, 'reviews_all'[review_timestamp] < date(2019,10,29) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,29) -30) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,29) -60, 'reviews_all'[review_timestamp] < date(2019,10,29) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,29) -30),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,29) -30),
"URL of recent reviews", CONCATENATE("https://amazon.ca/product-reviews/", CONCATENATE(CALCULATE(FIRSTNONBLANK('reviews_all'[SKU],'reviews_all'[SKU]), 'reviews_all'[review_rating] <= 3), "/ref=cm_cr_arp_d_viewopt_sr?sortBy=recent&pageNumber=1"))),

SUMMARIZE(FILTER(reviews_all,[eRetailer]<>"amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,10,29),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,29) -30),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,29) -60, 'reviews_all'[review_timestamp] < date(2019,10,29) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,29) -30) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,29) -60, 'reviews_all'[review_timestamp] < date(2019,10,29) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,29) -30),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,29) -30),
"URL of recent reviews", ""),


SUMMARIZE(FILTER(reviews_all,[eRetailer]="amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,10,22),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,22) -30),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,22) -60, 'reviews_all'[review_timestamp] < date(2019,10,22) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,22) -30) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,22) -60, 'reviews_all'[review_timestamp] < date(2019,10,22) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,22) -30),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,22) -30),
"URL of recent reviews", CONCATENATE("https://amazon.ca/product-reviews/", CONCATENATE(CALCULATE(FIRSTNONBLANK('reviews_all'[SKU],'reviews_all'[SKU]), 'reviews_all'[review_rating] <= 3), "/ref=cm_cr_arp_d_viewopt_sr?sortBy=recent&pageNumber=1"))),

SUMMARIZE(FILTER(reviews_all,[eRetailer]<>"amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,10,22),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,22) -30),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,22) -60, 'reviews_all'[review_timestamp] < date(2019,10,22) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,22) -30) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,22) -60, 'reviews_all'[review_timestamp] < date(2019,10,22) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,22) -30),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,22) -30),
"URL of recent reviews", ""),


SUMMARIZE(FILTER(reviews_all,[eRetailer]="amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,10,15),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,15) -30),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,15) -60, 'reviews_all'[review_timestamp] < date(2019,10,15) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,15) -30) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,15) -60, 'reviews_all'[review_timestamp] < date(2019,10,15) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,15) -30),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,15) -30),
"URL of recent reviews", CONCATENATE("https://amazon.ca/product-reviews/", CONCATENATE(CALCULATE(FIRSTNONBLANK('reviews_all'[SKU],'reviews_all'[SKU]), 'reviews_all'[review_rating] <= 3), "/ref=cm_cr_arp_d_viewopt_sr?sortBy=recent&pageNumber=1"))),

SUMMARIZE(FILTER(reviews_all,[eRetailer]<>"amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,10,15),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,15) -30),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,15) -60, 'reviews_all'[review_timestamp] < date(2019,10,15) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,15) -30) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,15) -60, 'reviews_all'[review_timestamp] < date(2019,10,15) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,15) -30),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,15) -30),
"URL of recent reviews", ""),


SUMMARIZE(FILTER(reviews_all,[eRetailer]="amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,10,8),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,8) -30, 'reviews_all'[review_timestamp] < date(2019,10,8)),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,8) -60, 'reviews_all'[review_timestamp] < date(2019,10,8) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,8) -30, 'reviews_all'[review_timestamp] < date(2019,10,8)) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,8) -60, 'reviews_all'[review_timestamp] < date(2019,10,8) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,8) -30, 'reviews_all'[review_timestamp] < date(2019,10,8)),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,8) -30, 'reviews_all'[review_timestamp] < date(2019,10,8)),
"URL of recent reviews", CONCATENATE("https://amazon.ca/product-reviews/", CONCATENATE(CALCULATE(FIRSTNONBLANK('reviews_all'[SKU],'reviews_all'[SKU]), 'reviews_all'[review_rating] <= 3), "/ref=cm_cr_arp_d_viewopt_sr?sortBy=recent&pageNumber=1"))),

SUMMARIZE(FILTER(reviews_all,[eRetailer]<>"amazon.ca"), [eRetailer], [product], [product_variation], [brand], [category],
"EmailAlertWeek", date(2019,10,8),
"Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,8) -30, 'reviews_all'[review_timestamp] < date(2019,10,8)),
"Past Past Month to Date", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,8) -60, 'reviews_all'[review_timestamp] < date(2019,10,8) -30),
"MoM Rating Change", CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,8) -30, 'reviews_all'[review_timestamp] < date(2019,10,8)) - CALCULATE(AVERAGE('reviews_all'[review_rating]),'reviews_all'[review_timestamp] > date(2019,10,8) -60, 'reviews_all'[review_timestamp] < date(2019,10,8) -30),
"Negative Review Titles", CALCULATE(CONCATENATEX('reviews_all',[review_title], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,8) -30, 'reviews_all'[review_timestamp] < date(2019,10,8)),
"Negative Reviews", CALCULATE(CONCATENATEX('reviews_all',[review_body], "; "),'reviews_all'[review_rating] <= 3, 'reviews_all'[review_timestamp] > date(2019,10,8) -30, 'reviews_all'[review_timestamp] < date(2019,10,8)),
"URL of recent reviews", "")
)

[v-chuncz-msft · Accepted solution]

@minnawu 

You may take a look at the posts below.

• https://community.powerbi.com/t5/Desktop/Time-difference-between-next-row/td-p/338621
• https://community.powerbi.com/t5/Desktop/Dax-to-Sql-Windowing-Lag-function-Over-partition-by-equival...
• https://community.powerbi.com/t5/Desktop/Finding-the-previos-occurrence-within-same-table-based-on-o...