Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sun-sboyanapall
Frequent Visitor

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(ConvertedDate,Date_of_Last_Status_Change__c)) <> MONTH(TransactionDate) and Year(ISNULL(ConvertedDate,Date_of_Last_Status_Change__c)) <> YEAR(TransactionDate) then DocumentNumber end))

 

TABLE STRUCTURE

IDTransactionDateConvertedDateDate_of_Last_Status_Change__cDocumentNumber
12312/1/202112/1/202112/15/2021001

145

11/1/202112/1/202112/15/2021002
23410/1/20219/1/202110/21/2021003
265NULL10/1/202110/15/2021NULL
NULL2/1/2021NULLNULL005

 

correct answer:

4 - 2 = 2

 

Thanks for your help!

 

 

1 ACCEPTED SOLUTION

@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
)

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

8 REPLIES 8
sun-sboyanapall
Frequent Visitor

Thank you, another way I did was created a calculated column, is consumed that is taking care of my case statement.

 

IsConsumed = 
var yearTranDate = YEAR(Tbl[TransactionDate])
var monthTranDate = MONTH(Tbl[TransactionDate])
var yearConvDate = YEAR(Tbl[ConvertedDate]))
var monthConvDate = MONTH(Tbl[ConvertedDate]))
var yeardateLastSC = YEAR(Tbl[Date_of_Last_Status_Change__c]))
var monthdateLastSC = MONTH(Tbl[Date_of_Last_Status_Change__c]))

RETURN  IF(ISBLANK(vLeadConsumedWithSales[ID]), FALSE(), IF(ISBLANK(yearConvDate), yearDateLastSC <> yearTranDate && monthDateLastSC <> monthTranDate, yearConvDate <> yearTranDate && monthConvDate <> monthTranDate))

@sun-sboyanapall  well done.

 

One rule of thumb to always remember with DAX; if you can create a measure, don't create a column.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

can you explain your measure too please, in its current form it is hard to understand. @smpa01 

@sun-sboyanapall 

 

Measure = 
    VAR _base =
        FILTER (    //#1- returns a table that has no tbl[ID]or tbl[ConvertedDate]or tbl[Date_of_Last_Status_Change__c] as BLANK
            tbl,
            tbl[TransactionDate] <> BLANK ()
                && tbl[ConvertedDate] <> BLANK ()
                && tbl[Date_of_Last_Status_Change__c] <> BLANK ()
        )
    VAR _left =
        FILTER ( tbl, tbl[ID] <> BLANK () ) //#2- returns a table that has no tbl[ID] as BLANK
    VAR _join = 
        EXCEPT ( // #4 - this returns a left anti joined tables between 2 and 3, i.e. return everything from 2 that are not in 3
            _left,
            FILTER ( // #3- previously created base table is further filteres with SQL IS NULL conditions
                _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 CALCULATE ( //5. DISTINCTCOUNT is performed on the above derived table
    DISTINCTCOUNT ( tbl[ID] ),_join)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

That's clear, Thank you Sir

smpa01
Super User
Super User

@sun-sboyanapall  I don't think the above SQL query returns 2 for the data provided, it returns 4, cause the following part returns 0

COUNT( DISTINCT (case when MONTH(ISNULL(ConvertedDate,Date_of_Last_Status_Change_c)) <> MONTH(TransactionDate) and Year(ISNULL(ConvertedDate,Date_of_Last_Status_Change_c)) <> YEAR(TransactionDate) then DocumentNumber end))

 

breakdown

MONTH(ISNULL(ConvertedDate,Date_of_Last_Status_Change_c)) <> MONTH(TransactionDate) 
has two matches row 2 and row 3 as {12,11}, {9,10}

Year(ISNULL(ConvertedDate,Date_of_Last_Status_Change_c)) <> YEAR(TransactionDate)
has no match as all of them are {2021,2021}

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.


@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
)

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.