Matrix Visual to show max values for each column in a wide format fact table

[magnussoerensen]

Hello,

I am working on a PBI App to display time series sensor data. Example:

Sensor1 Sensor2 Sensor3 Timestamp

2	2	4	1/1/2022
3	2	5	2/1/2022
5	3	6	3/1/2022
7	4	7	4/1/2022
3	2	4	5/1/2022
1	3	5	6/1/2022
2	4	6	7/1/2022
4	5	7	8/1/2022
6	2	8	9/1/2022
8	4	5	10/1/2022

Right now I have multiple fact tables because some of the data need to be in a long format fact table (to make it possible to make a Matrix Visual showing max values for each sensor), but I would prefer to have a single wide format fact table. Therefore, I seek advice on how to create this Matrix Visual with a single column showing maximum values using a wide format fact table. Each row in the visual should be named after a sensor which has its own column in the fact table and the values should be the maximum for each sensor within a filtered date range.

The images show my current solution. Basically I want to avoid making multiple queries to the Azure DB that stores the data by having a single fact table.

Long format fact table

Matrix Visual and date filter

Measure used to calculate max value when data is in long format:

Max =

VAR startdate = MIN('Long format'[Timestamp])

VAR enddate = MAX('Long format'[Timestamp])

VAR result = CALCULATE(

Max('Long format'[Value]),

FILTER(

'Long format',

'Long format'[Timestamp]>=startdate &&

'Long format'[Timestamp]<=enddate

)

)

Return result

How can I make exactly the same Matrix Visual when my fact table looks like this 

Best regards,

Magnus

[amitchandak]

@magnussoerensen , In the case of log format you have to move sensor on the column of Matrix , if time is coming from this table or date table only joined with datevalue { datevalue([Timestamp) }, then you can use date on row and just take max, no need to write filter code

[magnussoerensen]

Are you saying that it is not possible to make this exact Matrix Visual with sensor names as rows and max values in the column when my fact table is in wide format? It will not work for me to transpose the matrix and give each sensor its own column in the visual because in one visual I have 17 sensors that need to be shown at the same time.

[v-rzhou-msft]

Hi  @magnussoerensen ,

If your fact table looks like Sensor1,Sensor2...Sensor17,Timestamp, you need to calculate max values from all Sensor columns, then add them into matrix value field. It is not a good idea.

I suggest you to use use Unpivot function in Power Query Editor. I agree with amitchandak 's reply above. According to your code before, I think you don't need to create a code to get max value, you just need to use MAX function in matrix visual value field. 

Steps is as below.

1. Select Timestamp and unpivot other columns.

2. Create matrix.

 

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[magnussoerensen]

Hello @v-rzhou-msft, I understand that I don't need the measure to show the max value and already made the change in my report. Thank you for the suggestion, but also be aware that this does not address my issue.

As you can see from my first image, my current solution is exactly to use the unpivot function like you suggest. I would like to avoid this. I have multible fact tables that I would like to join so that I only have 1 fact table that can store all my sensor data (to avoid multible queries to the Azure database and to avoid dublicate data in PBI). That is why I am looking for a way to make this matrix visual from a wide format fact table. If this is not currently possible or if it is very ill-advised then I will have to rethink my plans.

Best regards,

Magnus