Date shows as #,0.00 in PowerBi desktop of some machines

[sipati]

Hi, any idea why Date in a card visual showing in some machines PowerBi Desktop as- 

In other machines (with same version of PowerBi Desktop) and also on PowerBi Workspace, it is showing as properly as date-

It is a measure-

DateMeasure = SELECTEDVALUE(SampleView[DateColumn]," ")

SampleView[DateColumn] is formatted as -

It is a coming from a view (Directquery SQL) as - 

CAST(ISNULL(DateColumn,'') AS VARCHAR(25))     AS DateColumn

[amitchandak · Accepted solution]

@sipati , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Can you try only

DateMeasure = SELECTEDVALUE(SampleView[DateColumn])

[v-alq-msft · Accepted solution]

Hi, @sipati 

Based on your description, I created data to reproduce your scenario. The data source is Sql Server(DirectQuery).

SampleTable:

Then you may create a measure as below.

Measure = SELECTEDVALUE(SampleTable[DateColumn],TODAY())

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@sipati , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Can you try only

DateMeasure = SELECTEDVALUE(SampleView[DateColumn])

[sipati]

Yeah.

Query-

CREATE TABLE dbo.SampleTable (
[Id] INT NOT NULL,
[DateColumn] DATETIME NOT NULL
);

INSERT INTO dbo.SampleTable (Id, DateColumn) VALUES (1,'7/1/2020 00:00:00 AM');
INSERT INTO dbo.SampleTable (Id, DateColumn) VALUES (2,'7/2/2020 00:00:00 AM');
INSERT INTO dbo.SampleTable (Id, DateColumn) VALUES (3,'7/3/2020 00:00:00 AM');

CREATE VIEW dbo.SampleView
AS
SELECT DISTINCT ISNULL(Id,'') AS Id,
CAST(ISNULL(DateColumn,'') AS VARCHAR(25)) AS DateColumn
FROM dbo.SampleTable

UNION

SELECT '' AS Id,
'' AS DateColumn
FROM (SELECT 1 AS ID)A
WHERE NOT EXISTS
(
SELECT DISTINCT ISNULL(Id,'') AS Id,
CAST(ISNULL(DateColumn,'') AS VARCHAR(25)) AS DateColumn
FROM dbo.SampleTable
)

PBIX (source: localhost; "test" database DirectQuery SQL):

Sample PBIX Link 

Yes, will try selectedvalue(SampleView[DateColumn]) instead of selectedvalue(SampleView[DateColumn], " ")

[amitchandak]

@sipati , any reason for casting datetime as varchar while bringing to power bi. Unless it is super needed do yo cast as varchar