Customer with the Highest Total Spending in Each City

 SELECT city, name, SUM(amount) AS TotalSpending

FROM customers_prc c

JOIN orders_prc o ON c.customerid = o.customer_id

GROUP BY city, name

HAVING TotalSpending = (SELECT MAX(TotalSpending) 

FROM (SELECT city, name, SUM(amount) AS TotalSpending 

        FROM customers_prc c JOIN orders_prc o ON c.customerid = o.customer_id GROUP BY city, name) AS Subquery 

WHERE Subquery.city = c.city);

Qlik filter values into comma separated values

 

 

Replace(

Concat({<[Aggregation Level 1] = p([Aggregation Level 1])>} [Aggregation Level 1], ',')

,'Region','region_name') 

to use the list as values/column name in the expressions

$(=$(Aggregationlist))

chiclet slicer to keep drill through selection for more than one page

Here is the video to update /keep drill through selection on for as many as pages we want. 

https://www.youtube.com/watch?v=uq0FozPBidE&t=273s

Date format with time zone in Power BI

Choose the date field and enter below expression in the dropdown. it will accept and change the format accordingly. 

my sample data is in EST and no hours difference and I just hardcoded EST at the end. conversion needed if the data tracked in the different time zone. 

mm/dd/yyyy hh:mm:ss AM/PM "EST"

Power BI slicer or filters are not working in another page

 

Sync filters by choosing a filter in any page and update as needed.