alerts = VAR EndOfWeekColumn = DISTINCT('DATE'[EndOfWeek]) RETURNUNIONX(SUMMARIZE(TABLE, Column1, Column2, etc, "New Column1", CALCULATE(...using the next value in EndOfWeekColumn...),"New Column2", CALCULATE(...using the next value in EndOfWeekColumn...)))

Anonymous 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-equivalent/td-p/387698 https://community.powerbi.com/t5/Desktop/Finding-the-previos-occurrence-within-same-table-based-on-one/m-p/342607#M153516
In DAX, can you loop through each value from a column variable in a SUMMARIZE table?

3 Replies

v-chuncz-msft
Community Support
6 years ago
Anonymous

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-equivalent/td-p/387698

https://community.powerbi.com/t5/Desktop/Finding-the-previos-occurrence-within-same-table-based-on-one/m-p/342607#M153516
Anonymous
Not applicable
6 years ago
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", "")
)
nizebelami
Regular Visitor
2 years ago
I also need a solution, I have the same problem
Forum Discussion

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

3 Replies

Related content

Using Variables in Summarize

Parameter in summarize

Summarize multiple variables

Don't Summarize....still summarizes...?

GroupBY and summarize
