Forum Discussion
Dynamically update Pie Chart with data from a direct query based on a time slicer
The data table below is retrived using Direct Query
Pump Activity Table (Active - 1 , Not Active - 0)
Date | Pump Number | Active / Not Active |
01/01/2019 | 1 | 1 |
02/01/2019 | 1 | 1 |
03/01/2019 | 1 | 0 |
04/01/2019 | 1 | 1 |
05/01/2019 | 1 | 1 |
01/01/2019 | 2 | 1 |
02/01/2019 | 2 | 1 |
03/01/2019 | 2 | 1 |
04/01/2019 | 2 | 1 |
05/01/2019 | 2 | 1 |
01/01/2019 | 3 | 1 |
02/01/2019 | 3 | 1 |
03/01/2019 | 3 | 1 |
04/01/2019 | 3 | 1 |
05/01/2019 | 3 | 0 |
We need to monitor the activity of the Pumps and display it in a pie chart according to its activity status.
The requirement Is :
If a user selects a particular time period using a slicer. The above pie chart should dynamically update itself displaying the Activity status of the pumps.
1. The green area should indicate the number of pumps which has been continously active over the selected time span.
i.e Active / Not Active column containing only 1s for the selected time period.
2. The Red area should display the number of pumps which have been inactive atleast once over the selected time span.
i.e. The Active /Not Active column contining atlease one 0 for the selected time period.
3. The Yellow area should display the number of pumps which have been inactive yesterday.
i.e The Active / Not Active column has a value of 0 for yesterday
Example/Expected result
Consider today's date is 06/01/2019.
If I Choose the time period 01/01/2019 to 06/01/2019 in the slicer, the following records will be considered for calculating the pie chart visualization. Today's record is not included because the table is updated at midnight.
Date | Pump Number | Active / Not Active |
|
01/01/2019 | 1 | 1 |
|
02/01/2019 | 1 | 1 |
|
03/01/2019 | 1 | 0 | This Pump should be included in the Red Area of the piechart because it has been inactive for atleast a day. |
04/01/2019 | 1 | 1 |
|
05/01/2019 | 1 | 1 |
|
01/01/2019 | 2 | 1 |
|
02/01/2019 | 2 | 1 |
|
03/01/2019 | 2 | 1 | This Pump should be included in the Green Area of the piechart because it has been active all the time |
04/01/2019 | 2 | 1 |
|
05/01/2019 | 2 | 1 |
|
01/01/2019 | 3 | 1 |
|
02/01/2019 | 3 | 1 |
|
03/01/2019 | 3 | 1 | This Pump should be included in the Yellow Area of the piechart because it has been inactive yesterday. |
04/01/2019 | 3 | 1 |
|
05/01/2019 | 3 | 0 |
|
We tried creating a measure and using it in the piechart legend field, but we were unsuccessfull.
Can the activity status of the pumps be visualised dynamically on the donut visual based on the time filter ?
Thank You
Hi Anonymous ,
If the donut visual should show the distinct number of pumps for each status, we can try to use the following measures:
PumpNumber = SWITCH ( SELECTEDVALUE ( LegandTable[LegandName] ), "Red", SUMX ( 'Pump Number', IF ( CALCULATE ( COUNTROWS ( RELATEDTABLE ( 'Pump' ) ), 'Pump'[Active / Not Active] = 0 ) > 0 && CALCULATE ( MAX ( 'Pump'[Active / Not Active] ), FILTER ( RELATEDTABLE ( 'Pump' ), 'Pump'[Date] = [Today] - 1 ) ) = 1, 1, BLANK () ) ), "Green", SUMX ( 'Pump Number', IF ( CALCULATE ( COUNTROWS ( RELATEDTABLE ( 'Pump' ) ), 'Pump'[Active / Not Active] = 1 ) = CALCULATE ( COUNTROWS ( RELATEDTABLE ( 'Pump' ) ) ), 1, BLANK () ) ), "Yellow", SUMX ( 'Pump Number', IF ( CALCULATE ( MAX ( 'Pump'[Active / Not Active] ), FILTER ( RELATEDTABLE ( 'Pump' ), 'Pump'[Date] = [Today] - 1 ) ) = 0, 1, BLANK () ) ) )
Best regards,
5 Replies
Hi Anonymous ,
We can try to use a measure after create two calculated table to meet your requirement:
Calculated Table:
LegandTable = DATATABLE("LegandName",STRING,{{"Red"},{"Green"},{"Yellow"}})Pump Number = DISTINCT('Pump'[Pump Number])Measures:
Today = DATE(2019,1,6) //this date is for test, you can replace it with TODAY()PumpNumber = SWITCH ( SELECTEDVALUE ( LegandTable[LegandName] ), "Red", SUMX ( 'Pump Number', IF ( CALCULATE ( COUNTROWS ( RELATEDTABLE ( 'Pump' ) ), 'Pump'[Active / Not Active] = 0 ) > 0 && CALCULATE ( MAX ( 'Pump'[Active / Not Active] ), FILTER ( RELATEDTABLE ( 'Pump' ), 'Pump'[Date] = [Today] - 1 ) ) = 1, [Pump Number], BLANK () ) ), "Green", SUMX ( 'Pump Number', IF ( CALCULATE ( COUNTROWS ( RELATEDTABLE ( 'Pump' ) ), 'Pump'[Active / Not Active] = 1 ) = CALCULATE ( COUNTROWS ( RELATEDTABLE ( 'Pump' ) ) ), [Pump Number], BLANK () ) ), "Yellow", SUMX ( 'Pump Number', IF ( CALCULATE ( MAX ( 'Pump'[Active / Not Active] ), FILTER ( RELATEDTABLE ( 'Pump' ), 'Pump'[Date] = [Today] - 1 ) ) = 0, [Pump Number], BLANK () ) ) )
Best regards,Hi Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,Hi Dong Li,
Sorry for the late reply.
I think you have misunderstood my requirements.
Requirement
The donut visual needs to represent the activity status of all the pumps.
Input Data
Expected Result
The desired result for the input data provided would be looking like the visual below. This is because there is only one pump satisfying each of the three Pump status conditions.
Thank You
Regards
Avinash
Hi Anonymous ,
If the donut visual should show the distinct number of pumps for each status, we can try to use the following measures:
PumpNumber = SWITCH ( SELECTEDVALUE ( LegandTable[LegandName] ), "Red", SUMX ( 'Pump Number', IF ( CALCULATE ( COUNTROWS ( RELATEDTABLE ( 'Pump' ) ), 'Pump'[Active / Not Active] = 0 ) > 0 && CALCULATE ( MAX ( 'Pump'[Active / Not Active] ), FILTER ( RELATEDTABLE ( 'Pump' ), 'Pump'[Date] = [Today] - 1 ) ) = 1, 1, BLANK () ) ), "Green", SUMX ( 'Pump Number', IF ( CALCULATE ( COUNTROWS ( RELATEDTABLE ( 'Pump' ) ), 'Pump'[Active / Not Active] = 1 ) = CALCULATE ( COUNTROWS ( RELATEDTABLE ( 'Pump' ) ) ), 1, BLANK () ) ), "Yellow", SUMX ( 'Pump Number', IF ( CALCULATE ( MAX ( 'Pump'[Active / Not Active] ), FILTER ( RELATEDTABLE ( 'Pump' ), 'Pump'[Date] = [Today] - 1 ) ) = 0, 1, BLANK () ) ) )
Best regards,