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