Problem with ALL/ALLSELECTED while creating a heatmap

[Pedro503]

Hey there.

Here's the file link used for this example: heatmap.pbix

I'm currently working on the creation of a heatmap by using a matrix. In order to do so, I'm trying to retrieve the moment with the highest income in a given week, but I need to consider both the week day (like Monday, Dec 9th; Tuesday, Dec 10th...) as well as the contact hour (in the column headers). 

With the following measure I've authored, it worked for both the week and the week day lines where the moment took place (in blue frame, in the second matrix), however I still need that the cells inside the orange frame display the number 79 (income of the busiest moment of the week, as highlighted in the green cell of the first matrix). 

heatmap = 
VAR _min_year = CALCULATE ( MIN ( f_tickets[date_start] ), REMOVEFILTERS () )
VAR _max_year = CALCULATE ( MAX ( f_tickets[date_start] ), REMOVEFILTERS () )
VAR _result =
    MAXX (
        CALCULATETABLE (
            CROSSJOIN (
                DISTINCT ( d_calendar[week_en_us] ),
                DISTINCT ( d_calendar[day_name_en_us_with_day_name] ),
                DISTINCT ( d_hours )
            ),
            d_calendar[year] >= YEAR ( _min_year ),
            d_calendar[year] <= YEAR ( _max_year ),
            ALLSELECTED ()
        ),
        [count_tickets] 
    )
RETURN
    _result

The desired outcome would be that all the numbers in the week shows the same number as the busiest moment, so in the example I provided all the cells shoud be showing 79 for the W50, Dec 9th, 24, not only the Monday, Dec 9th row (and the week header). For the Week 49 (W49 Dec 2nd, 24), all cells shoud be showing the the number 91. For the Week 48, 1693 and so on. 

Thanks in advance (= 

[v-yiruan-msft · Accepted solution]

Hi @Pedro503 ,

Please update the formula of your measure [heatmap] as below and check if it can return the correct result. Please find the details in the attachment.

heatmap = 
VAR __tbl =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            'd_calendar'[week_en_us],
            'd_calendar'[day_name_en_us_with_day_name],
            'd_hours'[day_moment],
            'd_hours'[hour],
            "@count_ticket",[count_tickets]
        ),
        ALLSELECTED(),
        VALUES('d_calendar'[week_en_us])
    )
VAR __result =
    MAXX ( __tbl, [@count_ticket] )
RETURN
    __result

Best Regards

[Pedro503]

Hey,  @v-yiruan-msft . Thanks a lot, that's exactly what I was trying to do 😃

Could you help me understand the reasoning used in the formula, please? Up to the ALLSELECTED() is fine to me, but what is the reason for using the VALUES() over the week column?

Once again, thanks for your help.

[v-yiruan-msft]

Hi @Pedro503 ,

The **VALUES('d_calendar'[week_en_us])** function is utilized to ensure that the calculation is performed for each individual week. This maintains the filter context for [week_en_us] from the outer environment, thereby ensuring that any filters applied to weeks are respected when the table is generated using SUMMARIZECOLUMNS.

Best Regards

[lbendlin]

 ALLSELECTED() is too broad.  Be more specific on which filter you want to remove (in your case it's the day name)

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Pedro503]

I don't think you've even read the entire message, as the link to the file is literally in the second line of my text. The sample data is in that file.

What did I include that is unrelated to the issue?