cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

convertir SQL(case) a Dax

Hola chicos,

Tengo esta consulta en sql que estoy tratando de hacer una medida en powerbi pero tengo problemas,

Consulta SQL:

SELECCIONE COUNT (DISTINCT(ID)) -

COUNT( DISTINCT (caso cuando MONTH(ISNULL(ConvertedDate,Date_of_Last_Status_Change__c)) <> MONTH(TransactionDate) y Year(ISNULL(ConvertedDate,Date_of_Last_Status_Change__c)) <> YEAR(TransactionDate) y luego DocumentNumber end))

ESTRUCTURA DE LA TABLA

IDENTIFICACIÓNTransactionDateConvertedDateDate_of_Last_Status_Change__cNúmero de documento
12312/1/202112/1/202112/15/2021001

145

11/1/202112/1/202112/15/2021002
23410/1/20219/1/202110/21/2021003
265NULO10/1/202110/15/2021NULO
NULO2/1/2021NULONULO005

respuesta correcta:

4 - 2 = 2

¡Gracias por su ayuda!

1 ACCEPTED SOLUTION

@sun-sboyanapall ¿Puedes probar esta medida?

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
)

View solution in original post

8 REPLIES 8
Syndicate_Admin
Administrator
Administrator

Gracias, otra forma que hice fue crear una columna calculada, se consume que se está ocupando de mi declaración de caso.

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 bien hecho.

Una regla general para recordar siempre con DAX; si puede crear una medida, no cree una columna.

¿Puede explicar su medida también, por favor, en su forma actual es difícil de entender? @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)

Eso está claro, gracias señor

Syndicate_Admin
Administrator
Administrator

@sun-sboyanapall No creo que la consulta SQL anterior devuelva 2 para los datos proporcionados, devuelve 4, porque la siguiente parte devuelve 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))

desintegración

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}

Eso es correcto, la respuesta correcta sería 4, pensé que he cambiado de año.

Espero con ansias cómo harías esto con dax.


@sun-sboyanapall ¿Puedes probar esta medida?

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
)

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors