Create Multiple Heatmaps in Power BI

[Alkhos]

Hello,

I have a question regarding creation of heatmaps in Power BI.

Assume that I have a dataset with the following four columns:

date   srcLocation  dstLocation   Value

The goals is to create heatmap for each date that is seen in the data. Here are the steps that I believe should be followed, and need some reference / help on either one:

1. Slice the data based on date, i.e. create a dataset for each unique date in the dataset

2. For each slice, transform the flat data into a matrix, i.e. column names should be srcLocation and row names should be destLocation and values should appear for each pair

3. Plot a heatmap that is sliced for each of the above matrices. Ideally, user can choose a date and see its heatmap.

Are there ways to achieve 1 and 2?

For 3, is the only way to create a visual in R? or does PBI have some heatmap visuals already?

[v-qiuyu-msft]

Hi @Alkhos,

1. Slice the data based on date, i.e. create a dataset for each unique date in the dataset

You can open Query Editor, right click the column [date] and select Remove Duplicates. Back to the report, place [date] in a slicer visual.

2. For each slice, transform the flat data into a matrix, i.e. column names should be srcLocation and row names should be destLocation and values should appear for each pair

You can just drag a matrix visual to the report page which has slicer visual, then place [srcLocation] in Columns and [destLocation] in Rows.

3. Plot a heatmap that is sliced for each of the above matrices. Ideally, user can choose a date and see its heatmap.

There is a custom visual named Table Heatmap, you can download from Visuals Gallery.

Best Regards,
Qiuyun Yu

[Alkhos]

Thanks @v-qiuyu-msft,

Steps 1 and 2 seems trivial, but I am confused on how to pass the matrix to the heatmap.

Ideally, I want to pass a dataset with the format similar created in step 2 to the "Table Heatmap" visual that you mentioned; however, the matrix is in my visuals not in query editor. Is there a way to send back these matrices to the available data so that they can be consumed by step 3?

Thanks

Ali

[v-qiuyu-msft]

Hi @Alkhos,

Can you share some sample data and expected data table? So that we can check whether we can achieve it in Query Editor.

Best Regards,
Qiuyun Yu

[Alkhos]

Hi @v-qiuyu-msft

Here is some sample data:

So I deally I want to have a heatmap ( similar to the matrix visual ) that has src for the rows and dest for the columns and average of values for all dates at the intersection points ( actually if matrix visual had conditional formatting like the table, it would have done the trick ). Then I want to have a slicer that can be used to filter (avergae) data for (a) specific day(s)

Bests

Ali

[v-qiuyu-msft · Accepted solution]

Hi @Alkhos,

In your scenario, you can use Treemap to display values like the matrix, and place Date field within the slicer visual to filter Treemap data. See:

Best Regards,
Qiuyun Yu

[Alkhos]

Hello,

I have a question regarding creation of heatmaps in Power BI.

Assume that I have a dataset with the following four columns:

date   srcLocation  dstLocation   Value

The goals is to create heatmap for each date that is seen in the data. Here are the steps that I believe should be followed, and need some reference / help on either one:

1. Slice the data based on date, i.e. create a dataset for each unique date in the dataset

2. For each slice, transform the flat data into a matrix, i.e. column names should be srcLocation and row names should be destLocation and values should appear for each pair

3. Plot a heatmap that is sliced for each of the above matrices. Ideally, user can choose a date and see its heatmap.

Are there ways to achieve 1 and 2?

For 3, is the only way to create a visual in R? or does PBI have some heatmap visuals already?