Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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/2...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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,