Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

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 cuan...
  • Syndicate_Admin's avatar
    Syndicate_Admin
    4 years ago

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

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    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.


    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      4 years ago

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