Forum Discussion

maximik's avatar
maximik
Frequent Visitor
2 years ago
Solved

How to count rows based on two criteria?

Hi all,

I am building my first PowerBI project and so far it works great.

Unfortunately, I know stumbled on a big problem and I am unsure on how to solve it.

 

I have a gigantic table, it looks sth like the table below.

It contains the name of an Azure Scale Set, the number of the child VM or instance within that scale set, the maximum CPU performance and the time stamp (datetime).

What I would like to do is to  display them in a graph, so you can click in a table e.g. on ScaleSet1 and you can see the CPU over time, in combination with the amount of underlying VMs. The kicker would be, if all "Max CPU performance" values of 0 are filtered out (as they cost no money), scaling is done via AKS, so no autoscale.

 

I guess what I need to do to get there:

- Calculate the number of instances per scale set (column 1) per hour (column 4) while filtering out "0" values.

- Then I have to plot it in a table, maybe with two Y-axis, one for CPU, and the other one for the number of VMs>0.

 

I am completely lost though how to tackle it.

Are there any ideas?

 

Virtual Machine Scale SetVM/Instance in that scale SetMax CPU performanceTime stamp (Date column)
ScaleSet1ScaleSet1_VM1501.01.2023_3pm
ScaleSet1ScaleSet1_VM21001.01.2023_3pm
ScaleSet1ScaleSet1_VM3001.01.2023_3pm
ScaleSet1ScaleSet1_VM12001.01.2023_4pm
ScaleSet1ScaleSet1_VM23001.01.2023_4pm
ScaleSet1ScaleSet1_VM31001.01.2023_4pm
ScaleSet2ScaleSet2_VM1501.01.2023_3pm
ScaleSet2ScaleSet2_VM21001.01.2023_3pm
ScaleSet2ScaleSet2_VM32001.01.2023_3pm
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi maximik 

    1.You can create a slicer table

    Slicer = {"Counts","Counts>0"}

    2.Then create a measure

    Counts =
IF (
    OR (
        ISFILTERED ( Slicer[Value] ) = FALSE (),
        SELECTEDVALUE ( Slicer[Value] ) = "Counts"
    ),
    CALCULATE ( COUNTA ( 'Table'[VM/Instance in that scale Set] ) ),
    IF (
        SELECTEDVALUE ( Slicer[Value] ) = "Counts>0",
        CALCULATE (
            COUNTA ( 'Table'[VM/Instance in that scale Set] ),
            FILTER ( 'Table', [Max CPU performance] > 0 )
        )
    )
)

    3.Then create a column visual, put the time to the x-axis, and put the measure to y-axis, then put the virtual to legend, and put the value of slicer table to a slicer.

    Output

     

    Best Regards!

    Yolo Zhu

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

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi maximik 

    1.You can create a slicer table

    Slicer = {"Counts","Counts>0"}

    2.Then create a measure

    Counts =
IF (
    OR (
        ISFILTERED ( Slicer[Value] ) = FALSE (),
        SELECTEDVALUE ( Slicer[Value] ) = "Counts"
    ),
    CALCULATE ( COUNTA ( 'Table'[VM/Instance in that scale Set] ) ),
    IF (
        SELECTEDVALUE ( Slicer[Value] ) = "Counts>0",
        CALCULATE (
            COUNTA ( 'Table'[VM/Instance in that scale Set] ),
            FILTER ( 'Table', [Max CPU performance] > 0 )
        )
    )
)

    3.Then create a column visual, put the time to the x-axis, and put the measure to y-axis, then put the virtual to legend, and put the value of slicer table to a slicer.

    Output

     

    Best Regards!

    Yolo Zhu

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