How to count rows based on two criteria?

[maximik]

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 Set	VM/Instance in that scale Set	Max CPU performance	Time stamp (Date column)
ScaleSet1	ScaleSet1_VM1	5	01.01.2023_3pm
ScaleSet1	ScaleSet1_VM2	10	01.01.2023_3pm
ScaleSet1	ScaleSet1_VM3	0	01.01.2023_3pm
ScaleSet1	ScaleSet1_VM1	20	01.01.2023_4pm
ScaleSet1	ScaleSet1_VM2	30	01.01.2023_4pm
ScaleSet1	ScaleSet1_VM3	10	01.01.2023_4pm
ScaleSet2	ScaleSet2_VM1	5	01.01.2023_3pm
ScaleSet2	ScaleSet2_VM2	10	01.01.2023_3pm
ScaleSet2	ScaleSet2_VM3	20	01.01.2023_3pm

[Anonymous · Accepted solution]

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.