Transform formula (Tableau > PBI)

[Anonymous]

I need to transform the formula below from Tableau to Power BI.

IF { FIXED DATETRUNC('hour', [Data do atendimento]), [Profissional] : SUM([Quantidade de atendimentos])} = 0 THEN 100 ELSEIF
{ FIXED DATETRUNC('hour', [Data do atendimento]), [Profissional] :SUM([Quantidade de atendimentos])} = 1 THEN 100 ELSEIF
{ FIXED DATETRUNC('hour', [Data do atendimento]), [Profissional] :SUM([Quantidade de atendimentos])} = 2 THEN 100 ELSEIF
{ FIXED DATETRUNC('hour', [Data do atendimento]), [Profissional] :SUM([Quantidade de atendimentos])} = 3 THEN 125 ELSEIF
{ FIXED DATETRUNC('hour', [Data do atendimento]), [Profissional] :SUM([Quantidade de atendimentos])} = 4 THEN 150 ELSEIF
{ FIXED DATETRUNC('hour', [Data do atendimento]), [Profissional] :SUM([Quantidade de atendimentos])} = 5 THEN 175 ELSEIF
{ FIXED DATETRUNC('hour', [Data do atendimento]), [Profissional] :SUM([Quantidade de atendimentos])} = 6 THEN 200 ELSEIF
{ FIXED DATETRUNC('hour', [Data do atendimento]), [Profissional] :SUM([Quantidade de atendimentos])} = 7 THEN 225 ELSEIF
{ FIXED DATETRUNC('hour', [Data do atendimento]), [Profissional] :SUM([Quantidade de atendimentos])} >= 8 THEN 250 ELSE
0 END

Thanks.

[harshnathani]

Hi @Anonymous ,

You can try using SWITCH () statement here.

Watch this video on how to use SWITCH() statement.

https://www.youtube.com/watch?v=-ykkaAtlCMc&vl=en

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi @harshnathani,
Thanks for the answer.
The problem is that I have to calculate how many calls the [Profissional] made in 1 hour, and then apply the condition. If [Profissional] attends 2 in an hour (= 100), 3 in an hour (= 125) ...

I have this information to work:

Profissional	Data do atendimento
XXX...	09/08/2020 15:33
YYY...	09/08/2020 20:56
XXX...	09/08/2020 18:53
YYY...	09/08/2020 19:25
XXX...	09/08/2020 19:10
XXX...	09/08/2020 20:49

Regards,
Fabrício de Dominicis

[Anonymous]

Hi @harshnathani 
Is it possible to do it in Power BI?

Regards,
Fabrício de Dominicis

[v-shex-msft · Accepted solution]

Hi @Anonymous,

So you mean you want to get the records count of the current category in one hour? If this is a case, you can extract the current date and hour with current category to calculate the count.

Measure =
VAR currDate =
    MAX ( table[Data do atendimento] )
VAR _count =
    CALCULATE (
        COUNTROWS ( table ),
        FILTER (
            ALLSELECTED ( table ),
            DATEVALUE ( [Data do atendimento] ) = DATEVALUE ( currDate )
                && HOUR ( [Data do atendimento] ) = HOUR ( curDate )
        ),
        VALUES ( table[Profissional] )
    )
RETURN
    SWITCH ( _count, 2, 100, 3, 125)

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft 
That worked.

Thanks!

Regards,
Fabrício