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 ,

 

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,

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    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,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Dong Li,

       

      Thanks for providing  a solution. This is a wonderful way to workaround the limitations of measures and visuals in Power BI.

       

      Thank You

      Regards

      Avinash