Count the Result of Measures using if or switch statement

[frittle]

Dear Power Bi Community,

As you can see below in the visual table, I have Timestamps of 20 Minutes (that were created using a list) and a Measure "Last Value Final 20 minute stamp", that gives out the value closest to each of the 20 minuteb timestamps (there are many values in my data).

I would like to create a switch function that groups each value this measure gives out in a percentage interval based of the maximum Value. Before using this 20 minute timestamp measure, I used a switch function that chose the value out of a column ("SPEICHERSTAND") in my table "Puffer Vergleich". See the old switch function below, that was only based on columns in my data table:

Puffer Füllstand = 
SWITCH(
    TRUE(),
    'Puffer Vergleich'[SPEICHERSTAND] <= 'Puffer Vergleich'[Max Value of Puffer]*0.2, "<20%",
    'Puffer Vergleich'[SPEICHERSTAND] > 'Puffer Vergleich'[Max Value of Puffer]*0.2 && 'Puffer Vergleich'[SPEICHERSTAND] < 'Puffer Vergleich'[Max Value of Puffer]*0.4, "20-40%",
    'Puffer Vergleich'[SPEICHERSTAND] >= 'Puffer Vergleich'[Max Value of Puffer]*0.4 && 'Puffer Vergleich'[SPEICHERSTAND] <= 'Puffer Vergleich'[Max Value of Puffer]*0.6, "40-60%",
    'Puffer Vergleich'[SPEICHERSTAND] > 'Puffer Vergleich'[Max Value of Puffer]*0.6 && 'Puffer Vergleich'[SPEICHERSTAND] < 'Puffer Vergleich'[Max Value of Puffer]*0.8, "60-80%",
    'Puffer Vergleich'[SPEICHERSTAND] >= 'Puffer Vergleich'[Max Value of Puffer]*0.8, ">80%"
)

Once I have this, I would like to count the occurances of each of these percentage Intervals, so that I can visuaualize these using a Donut Chart that looks like this:

Is it possible to use the switch function when grouping and then counting the results of a measure? I would really appreciate any help or input on this problem. many Thanks in Advance!

Best Regards
Leo

[tamerj1]

@frittle 
Would you please insert the [Puffer Max Dummy] Measure into the table to see its values?

[frittle]

@tamerj1 
Good point! It somehow doesn't take the maximum value for the ones it then obviously shows wrong. I think I know why this is the case also. My measure "Last Value Final 20 Minute Stamp" gives out the last value it finds, if there is no value in the specific timestamp (because that just means the buffer has not changed, the data I pull via SQL only gives a new value once there is a change to the buffer)

Last Value final 20 minute stamp = 
VAR OverallMaxDateTime =
    CALCULATE (
        MAXX ( 'Puffer Vergleich', 'Puffer Vergleich'[Datum] + 'Puffer Vergleich'[20 min timestamp]),
        REMOVEFILTERS ()
    )
VAR MaxDate =
    MAX ( 'Main Date Table'[Datum] )
VAR MaxTime =
    MAX ( '20 min timestamp'[20 Min timestamps])
VAR MaxDateTime =
    MaxDate + MaxTime
RETURN
    IF (
        MaxDateTime <= OverallMaxDateTime,
        VAR PastDateTime =
            FILTER (
                CALCULATETABLE (
                    SUMMARIZE (
                        'Puffer Vergleich',
                        'Main Date Table'[Datum],
                        '20 min timestamp'[20 Min timestamps]
                    ),
                    'Main Date Table'[Datum] <= MaxDate,
                    REMOVEFILTERS ( '20 min timestamp' )
                ),
                'Main Date Table'[Datum] + '20 min timestamp'[20 Min timestamps] <= MaxDateTime
            )
        VAR LatestDateTimeWithValue =
            TOPN (
                1,
                PastDateTime,
                'Main Date Table'[Datum] + '20 min timestamp'[20 Min timestamps]
            )
        VAR Result =
            CALCULATE (
                [Speicher Single Value Breaking ties 20 minute stamps],
                LatestDateTimeWithValue,
                REMOVEFILTERS ( '20 min timestamp' ) -- Time filters must be explicitly removed
            )
        RETURN
            Result
    )

Speicher Single Value Breaking ties 20 minute stamps = 
IF (
    -- If a single Date/Time is currently filtered
    COUNTROWS (
        SUMMARIZE ( 'Puffer Vergleich','Main Date Table'[Datum],'20 min timestamp'[20 Min timestamps] )
    ) = 1,
    -- Use Timestamp Index to break ties.
    LASTNONBLANKVALUE ( 'Puffer Vergleich'[20 min stamp index], [Speicher Single Value Select] )
)

[tamerj1]

@frittle 

Remove the column and keep the measure

[frittle]

@tamerj1 
Would it be possible to change the variable of the measure according to the slicer selection of BESCHREIBUNG like i tried here using if statements? (it doesn't work)

Puffer Füllstand test = 
VAR CurrentValue = [Last Value final 20 minute stamp]
IF(SELECTEDVALUE('Puffer Vergleich'[BESCHREIBUNG]) = "HB-G2x Speicher Bahn Zu-Ablauf", VAR MaxPufferInRage = 146 && 
IF(SELECTEDVALUE('Puffer Vergleich'[BESCHREIBUNG]) = "VB-G2x Speicher Zu-Ablauf", VAR MaxPufferInRage = 63
RETURN
    SWITCH (
        TRUE (),
        CurrentValue <= MaxPufferInRage * 0.2, "<20%",
        CurrentValue > MaxPufferInRage * 0.2
            && CurrentValue < MaxPufferInRage * 0.4, "20-40%",
        CurrentValue >= MaxPufferInRage * 0.4
            && CurrentValue <= MaxPufferInRage * 0.6, "40-60%",
        CurrentValue > MaxPufferInRage * 0.6
            && CurrentValue < MaxPufferInRage * 0.8, "60-80%",
        CurrentValue >= MaxPufferInRage * 0.8, ">80%"
    )

[tamerj1]

@frittle 

There is no need to do that. Just remove the "Max Value of Puffer" column from the table visual and add the measure you created instead

Puffer Max = 
MAX ( 'Puffer Vergleich'[Max Value of Puffer] )

Then use the measure

Puffer Füllstand test = 
VAR CurrentValue = [Last Value final 20 minute stamp]
VAR MaxPufferInRage = [Puffer Max]
RETURN
    SWITCH (
        TRUE (),
        CurrentValue <= MaxPufferInRage * 0.2, "<20%",
        CurrentValue > MaxPufferInRage * 0.2
            && CurrentValue < MaxPufferInRage * 0.4, "20-40%",
        CurrentValue >= MaxPufferInRage * 0.4
            && CurrentValue <= MaxPufferInRage * 0.6, "40-60%",
        CurrentValue > MaxPufferInRage * 0.6
            && CurrentValue < MaxPufferInRage * 0.8, "60-80%",
        CurrentValue >= MaxPufferInRage * 0.8, ">80%"
    )

[frittle]

@tamerj1 

This gives me the same problem I had yesterday. I think I need some kind of Last non blank function for the last value

[tamerj1]

@frittle 

Any chance to share sample file?

[tamerj1]

@frittle 

What is the formula of the calculated column?

[frittle]

@tamerj1 
I used the create conditional column in edit query. This is the formula it created:

Table.AddColumn(#"Added Index", "Max Value of Puffer", each if [BESCHREIBUNG] = "HB-G2x Speicher Bahn1-6" then 96 else if [BESCHREIBUNG] = "VB-G2x_Bahn 1 bis 3" then 48 else if [BESCHREIBUNG] = "BG-Speicher G2x Bahn 1-6 Gesamt" then 77 else if [BESCHREIBUNG] = "KG-Speicher Bahn 1-6 Gesamt" then 108 else if [BESCHREIBUNG] = "KG2-Speicher Bahn 1-5 Gesamt" then 33 else if [BESCHREIBUNG] = "KB1-Speicher Bahn 1-5 Gesamt" then 55 else if [BESCHREIBUNG] = "KB2-Speicher Bahn 1-6 Gesamt" then 72 else if [BESCHREIBUNG] = "KB3-Speicher Bahn 1-4 Gesamt" then 98 else if [BESCHREIBUNG] = "Finish-Speicher 1" then 113 else if [BESCHREIBUNG] = "Finish-Speicher 2" then 132 else if [BESCHREIBUNG] = "Finish-Speicher 1 LG" then 175 else if [BESCHREIBUNG] = "VB-G2x Speicher Zu-Ablauf" then 63 else if [BESCHREIBUNG] = "HB-G2x Speicher Bahn Zu-Ablauf" then 146 else if [BESCHREIBUNG] = "BG-Speicher G2x Bahn 1-6 Zu-Ablauf" then 139 else if [BESCHREIBUNG] = "KG2-Speicher Bahn 1-5 Gesamt Zu-Ablauf" then 66 else if [BESCHREIBUNG] = "KB1-Speicher Bahn 1-5 Zu-Ablauf" then 105 else if [BESCHREIBUNG] = "KB-Speicher Engpass Analyse" then 134 else if [BESCHREIBUNG] = "KB3-Speicher Bahn 1-4 Zu-Ablauf" then 139 else null)

[tamerj1]

@frittle 
I can see they are fixed values based on the value of the [BESCHREIBUNG] column. 

[tamerj1]

@frittle 

Can please you explain in words how is it supposed to be calculated?

[frittle]

@tamerj1 
I have several different buffers with different names (BESCHREIBUNG) and they all are different in size. The maximum capacity of a buffer is a fixed value, for example the buffer 

 "HB-G2x Speicher Bahn 1-6 " can hold up to 96 units. Throughout the day the value in this buffer changes (this is for a production analysis). I want to compare the different buffers and see in which fill levels based off of each of their maximums they are in mostly, so that I know which buffers tend to be rather full or empty. Since each buffer has a different size, they have a different maximum value. When I choose the name of my buffer in this slicer, I want it to choose the right maximum value automatically, so that the percentage calculation is right for the selected buffer. I need this, so that I can count the number of occurances (how often <20%, 20-40%....) so that I can create this kind of donut chart that shows which buffer fill level based off of the maximum I tend to be in. I have to use my measure "Last Value final 20 minute stamps though", because I only want to do this with the values I get in 20 minute intervalls, so that I always have the same amount of counting points per day (72 values per 24 hours becuase it gives me a value every 20 minutes).

[tamerj1]

Hi @frittle 

can you please provide a screenshot of your source data?

[frittle]

Hi @tamerj1 ,

This is what my source data looks like: