KPI for blank

[BhavyaM]

Hi Friends,

Company	Current	Previous	Percentage	Trend
A	10	20	-1	Down arrow
B	90	30	2	Uparrow
C
D	130	40	2.25	Uparrow

If there is data then below i am getting as expected.

But, I want to show Like below if there is no values.

Trend =

VAR _Trend = CALCULATE(SUM('Table'[Percentage]))

Return

IF(_Trend > 0 ,UNICHAR(9650) ,

IF(_Trend <= 0 ,UNICHAR(9660),

Blank()

))

Here, Condition is i should not convert Blanks to Zero.

Please help. Its an Urgent requirement

Thanks in advance.

[Fowmy]

@BhavyaM 

Modify your trend measure like:

Trend = 
VAR _Trend = CALCULATE(SUM('Table'[Percentage]))
Return
SWITCH( TRUE(),
    _Trend = BLANK(), "",
    _Trend > 0 ,UNICHAR(9650) ,
    _Trend <= 0 ,UNICHAR(9660)
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[BhavyaM]

@Fowmy 

I tried this,

But i am getting (Blank) for Percentage column in the advanced card visual.

[Fowmy]

@BhavyaM 

Share you a sample PBIX file to simulating the issue?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[BhavyaM]

@Fowmy 

I am not able to upload my Pbix file.

[Fowmy]

@BhavyaM 

You can drop it in One Drive or Google Drive then share the link here.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[BhavyaM]

@Fowmy

Please find the link below

https://1drv.ms/u/s!AsqJ-rVwhHrhaBtF_Sn9m6W2LUk 

Thanks in advance

[Fowmy]

@BhavyaM 

Hi,

I modified the Formula for _Diff: Try this

_Diff = 

VAR diff = DIVIDE([_Current]-[_Previous],[_Previous])

return

IF(ISBLANK(diff),"",diff)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[BhavyaM]

 @Fowmy 

I have tested the same query. 

But if the percentage is 0% then i am not getting any KPI icon. 

If Greater than 0% then my result is Update arrow and 

If less than 0% then Down arrow and

If blank() then ""

[Fowmy]

@BhavyaM 

What do like to see if the KPI is ZERO?

[BhavyaM]

Red colour Down arrow

[Fowmy · Accepted solution]

@BhavyaM 

Try this modified measure:

_Trend = 
SWITCH( TRUE(),
    [_Diff] = 0, UNICHAR(9660),
    [_Diff] = BLANK(), "",   
    [_Diff] > 0 ,UNICHAR(9650) ,
    [_Diff] < 0 ,UNICHAR(9660)
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[BhavyaM]

@Fowmy 

Thank you very much it help me and working now.

I am facing one issue in Data set refresh time, Can you please help me. this is very urgent requiremnt which i need to fix this soon. I am trying but not getting solution.

I used below Query to show the Last Data reset refresh time.

let
Source=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDLCQAhDAV78Sxonv9axP7bWLNPMN4mDMYhc7oUBAFRmvNOJBTy8r8RnTpNJh8TdRo0iUzT94BIIeTzRBdAaBr5GF0A0FTyMZqGdNM2mwDkG7CZZuhQKEA2ZdWI+pQ1U9ae/7v5v9vTYNzTYNiyFG/Z5rU+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dtDSTStart = _t, dtDSTEnd = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"dtDSTStart", type date}, {"dtDSTEnd", type date}}),
varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),+4)),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
varDSTOffset = Table.RowCount(#"Filtered Rows"),
#"Last Refresh Date" = #table(
type table
[
#"RefreshDate"=datetimezone
],
{
{DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),+4 + varDSTOffset,0)}
}
)
in
#"Last Refresh Date"

I used below measure

Last Update = "Last Update on " & UNICHAR(10) & FORMAT(MAX('Refresh Time Stamp'[RefreshDate]),"MMM DD, YYYY H:MM AM/PM")

But i am getting 30mins difference. For example, My Data set refresh time is "26/07/2020 8:30pm" and My system date and Time also same but my measure is showing me "26/07/2020 8:00PM".

How to show the date and time as per the Refresh date and time.

Please help me.

Thanks in advance.

[Fowmy]

@BhavyaM 

Try adjusting the +4 in your query