cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alkhos Frequent Visitor
Frequent Visitor

Create Multiple Heatmaps in Power BI

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Create Multiple Heatmaps in Power BI

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:

 

q1.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Highlighted
Alkhos Frequent Visitor
Frequent Visitor

Create Multiple Heatmaps in Power BI

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?

 

Moderator v-qiuyu-msft
Moderator

Re: Create Multiple Heatmaps in Power BI

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.

 

q1.PNG

 

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

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Alkhos Frequent Visitor
Frequent Visitor

Re: Create Multiple Heatmaps in Power BI

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

Moderator v-qiuyu-msft
Moderator

Re: Create Multiple Heatmaps in Power BI

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Alkhos Frequent Visitor
Frequent Visitor

Re: Create Multiple Heatmaps in Power BI

Hi @v-qiuyu-msft

 

Here is some sample data:

 

Untitled.png

 

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

Moderator v-qiuyu-msft
Moderator

Re: Create Multiple Heatmaps in Power BI

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:

 

q1.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.