Forum Discussion

sun-sboyanapall's avatar
sun-sboyanapall
Advocate I
4 years ago
Solved

convert SQL(case) to Dax

Hello guys,    I have this query in sql which I am trying to make a measure in powerbi but having issues,   SQL query:  SELECT COUNT (DISTINCT(ID)) -  COUNT( DISTINCT (case when MONTH(ISNULL(Co...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    sun-sboyanapall  can you try this measure

     

    Measure =
CALCULATE (
    DISTINCTCOUNT ( tbl[ID] ),
    VAR _base =
        FILTER (
            tbl,
            tbl[TransactionDate] <> BLANK ()
                && tbl[ConvertedDate] <> BLANK ()
                && tbl[Date_of_Last_Status_Change__c] <> BLANK ()
        )
    VAR _left =
        FILTER ( tbl, tbl[ID] <> BLANK () )
    VAR _join =
        EXCEPT (
            _left,
            FILTER (
                _base,
                MONTH ( COALESCE ( tbl[ConvertedDate], tbl[Date_of_Last_Status_Change__c] ) )
                    <> MONTH ( tbl[TransactionDate] )
                    && YEAR ( COALESCE ( tbl[ConvertedDate], tbl[Date_of_Last_Status_Change__c] ) )
                        <> YEAR ( tbl[TransactionDate] )
            )
        )
    RETURN
        _join
)

     

sun-sboyanapall's avatar
sun-sboyanapall
Advocate I
4 years ago

That's correct the correct answer would be 4, I thought I have changed year.

Looking forward to how you would do this with dax.


smpa01's avatar
smpa01
Community Champion
4 years ago

sun-sboyanapall  can you try this measure

 

Measure =
CALCULATE (
    DISTINCTCOUNT ( tbl[ID] ),
    VAR _base =
        FILTER (
            tbl,
            tbl[TransactionDate] <> BLANK ()
                && tbl[ConvertedDate] <> BLANK ()
                && tbl[Date_of_Last_Status_Change__c] <> BLANK ()
        )
    VAR _left =
        FILTER ( tbl, tbl[ID] <> BLANK () )
    VAR _join =
        EXCEPT (
            _left,
            FILTER (
                _base,
                MONTH ( COALESCE ( tbl[ConvertedDate], tbl[Date_of_Last_Status_Change__c] ) )
                    <> MONTH ( tbl[TransactionDate] )
                    && YEAR ( COALESCE ( tbl[ConvertedDate], tbl[Date_of_Last_Status_Change__c] ) )
                        <> YEAR ( tbl[TransactionDate] )
            )
        )
    RETURN
        _join
)