CODEWITHSUNDEEP
sqldatepowerbidax
ShayD
ShayD

Reputation: 799

PowerBI DAX result is different from SQL result with the same logic when filtering early dates

I have a measure in PowerBI that filtering records based on a date field earlier than 2009. The code variables were renamed for readability.

myMeasure = 
    CALCULATE(  
        DISTINCTCOUNT('myFactTable'[RecordID])
        , FILTER(
            All('myFactTable'),
            'myFactTable'[myDatefield] < DATEVALUE("01/01/2009")
        )        
    )

The result of the measure as shown in a card visual is 62

I used the following SQL query on the same data source assuming it will return identical results:

SELECT 
    COUNT(*) OldrecordsCount
FROM 
(
    SELECT DISTINCT 
        RecordID
    FROM 
        myFactTable
    WHERE
        myDatefield < '2009-01-01'          
) tbl1

But not as expected the result was 2

Does the SQL query equivalent to the dax one? Probably not, What could be the reason? Does PowerBI treats old dates differntly? What do I miss?

When using the same query but reversing the conditions to myDatefield > '2009-01-01' and 'myFactTable'[myDatefield] > DATEVALUE("01/01/2009") the results were both the same value, 2034.

Upvotes: 1

Views: 155

Answers (2)

Jos Woolley
Jos Woolley

Reputation: 9062

For any blanks within myDatefield, DAX will return TRUE for the comparison

myDatefield < DATEVALUE("01/01/2009")

Hence you should include an additional statement to check for non-blanks, e.g.:

=
CALCULATE(
    DISTINCTCOUNT( 'myFactTable'[RecordID] ),
    FILTER(
        ALL( 'myFactTable' ),
        'myFactTable'[myDatefield] < DATEVALUE( "01/01/2009" )
            && NOT ( ISBLANK( 'myFactTable'[myDatefield] ) )
    )
)

Upvotes: 2

максим ильин
максим ильин

Reputation: 194

get query :

SELECT count(*) as OldrecordsCount,
        RecordID
    FROM 
        myFactTable
    WHERE
        myDatefield < '2009-01-01' 
group by RecordID

write the result here

Upvotes: 0

Related Questions