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

[frittle]

@tamerj1 

Many thanks for taking the time and having the patience to help me, my problem is finally solved. Just to let you know, this measure fixed my blank values: 

Last Max Value = 
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( '20 min timestamp'[20 Min timestamps] ),
        FILTER (
            ALL ( '20 min timestamp'[20 Min timestamps] ),
            '20 min timestamp'[20 Min timestamps] <= MAX ( '20 min timestamp'[20 Min timestamps] )
                && '20 min timestamp'[Puffer Max Dummy] <> 0
        )
    )
RETURN
    CALCULATE (
        VALUE( [Puffer Max Dummy] ),
        FILTER ( ALL ( '20 min timestamp' ), '20 min timestamp'[20 Min timestamps] = LastNonBlankDate )
)

[frittle]

@tamerj1 
Unfortunately sharing data is not possible due to privacy reasons of my company. I now created a Table that has two columns, one for the name of my buffer and the other contains the maximum value. I want my "dummy measure" to select the right maximum value if my slicer is set to the name of the buffer, could you help me find the right dummy measure? (I already connected my MAXTABLE to my main table)
I tried 

Puffer Max Dummy = SELECTEDVALUE(MAXTABLE[MAXWERT])

but it's not working properly, do I need a filter?

[tamerj1 · Accepted solution]

@frittle 
Great! The only thing you need to do now is to use the [BESCHREIBUNG] column from the Dim. MAXTABLE instead. It should work. Otherwise if you are still interested in slicing by the [BESCHREIBUNG] column from the fact. 'Puffer Vergleich' table then you need to crossfilter the relationship. 

Puffer Max Dummy =
CALCULATE (
    SELECTEDVALUE ( MAXTABLE[MAXWERT] ),
    CROSSFILTER ( MAXTABLE[BESCHREIBUNG], 'Puffer Vergleich'[BESCHREIBUNG], BOTH )
)

[frittle]

@tamerj1 
Perfekt! This is what I was looking for, Thanks a lot. There is still one small problem, for the values where my measure "Last Value Final 20 minute stamp" gives me the last non blank value (becuase it didn't fina a value for the corresponding timestamp) the maximum does not show as you can see in my dashboard table below. can I use some kind of last non blank measure for the maxcimum value as well?

[tamerj1]

Yes try replacing SELECTEDVALUE with FIRSTNOBLANKROW

[frittle]

It does not allow that function

[tamerj1]

FIRSTNONBLANK ( <ColumnName>, <Expression> )

[frittle]

what is my expression in this case?

[tamerj1]

Puffer Max Dummy =
FIRSTNONBLANK ( MAXTABLE[MAXWERT], SELECTEDVALUE ( MAXTABLE[MAXWERT] ) )

[frittle]

This is not giving me any maximum value. Any way to combine the firstnonblank function with this one? :

Puffer Max Dummy = 
CALCULATE (
    SELECTEDVALUE ( MAXTABLE[MAXWERT] ),
    CROSSFILTER ( MAXTABLE[BESCHREIBUNG], 'Puffer Vergleich'[BESCHREIBUNG], BOTH )
)

[tamerj1]

@frittle 

Not sure which maximum value to compare with but I did the calculation based on the maximum value of the selected range (current range in the filter)

Puffer Füllstand =
VAR CurrentValue = [last Value tingl 20 minute stamo]
VAR MaxPufferInRage =
    MAX ( 'Puffer Vergleich'[SPEICHERSTAND] )
RETURN
    SWITCH (
        TRUE (),
        CurrentValue <= 'Puffer Vergleich'[Max Value of Puffer] * 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 
Many Thanks for taking the time to look at this problem. As further background:  The measure "Last Value in 20 min stamp" gives me the Value of "SPEICHERSTAND", that comes closest to one of the 20 Minute timestamps in 24 hours. This means that this value gives me 72 values every 24 hours. I am comparing the fill level of different buffers that are different in size, and therefore have different maximums. This is why the conditional column "Max Value of Puffer" exists, it contains a fixed maximum value for each buffer:

The goal with the donut chart in the end is to see in what percentage ranges based off of the maximum of the selected buffer and timerange (both unsing slicer) I am in. I have tried your measure and it is not taking my conditional column "Max value of Puffer" in the switch function (it only allows for measures to be chosen here). Any ideas why this is the case?

[tamerj1]

@frittle 

Yes I explained this in my reply. I the wa calculating the max value avialable in the filter from the [SPEICHERSTAND] column. If I understand correctly you may use this code instead 

Puffer Füllstand =
VAR CurrentValue = [last Value tingl 20 minute stamo]
VAR MaxPufferInRage =
    MAX ( 'Puffer Vergleich'[Max Value of Puffer] )
RETURN
    SWITCH (
        TRUE (),
        CurrentValue <= 'Puffer Vergleich'[Max Value of Puffer] * 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]

I still have the same problem. This is the error message I meant. I think it's due to the fact that there are different Maximum Values with different names inside one column... any ideas?

[tamerj1]

Sorry @frittle 

that was a typo mistake. Please try

Puffer Füllstand =
VAR CurrentValue = [last Value tingl 20 minute stamo]
VAR MaxPufferInRage =
    MAX ( 'Puffer Vergleich'[Max Value of Puffer] )
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%"
    )

[v-cgao-msft]

Hi @frittle ,

I'm not sure if this will solve the problem, try this.

Puffer Füllstand =
VAR CurrentValue = [last Value tingl 20 minute stamo]
VAR MaxPufferInRage =
    MAX ( 'Puffer Vergleich'[SPEICHERSTAND] )
RETURN
    SWITCH (
        TRUE (),
        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%",
        "<20%"
    )

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Ho to get yur questions answered quickly -- How to provide sample data

[frittle]

@tamerj1 @v-cgao-msft 

Many thanks for your replies guys. This seems to work for the most part, however it has a problem with the 0.8* max value somehow. It sometimes confuses the 60-80% with the >80% range, as you can see in the screenshot below. Any ideas what could be going wrong? Should I choose a different logic for the maximum values than a conditional column? 

Puffer Füllstand test = 
VAR CurrentValue = [Last Value final 20 minute stamp]
VAR MaxPufferInRage =
    MAX ( 'Puffer Vergleich'[Max Value of Puffer] )
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 
Try to refer to the max measure 

Puffer Füllstand test = 
VAR CurrentValue = [Last Value final 20 minute stamp]
VAR MaxPufferInRage = [Max Value of Puffer]
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]

This doesn't work since "Max Value of Puffer" is a conditional column, not a measure. I tried to create a dummy measure to try it and it gives me the same result. 

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

Puffer Füllstand test = 
VAR CurrentValue = [Last Value final 20 minute stamp]
VAR MaxPufferInRage = [Puffer Max Dummy]
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]

Here is another example for a different buffer where the measure is showing wrong values when considering the maximum