Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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ÓN | TransactionDate | ConvertedDate | Date_of_Last_Status_Change__c | Número de documento |
123 | 12/1/2021 | 12/1/2021 | 12/15/2021 | 001 |
145 | 11/1/2021 | 12/1/2021 | 12/15/2021 | 002 |
234 | 10/1/2021 | 9/1/2021 | 10/21/2021 | 003 |
265 | NULO | 10/1/2021 | 10/15/2021 | NULO |
NULO | 2/1/2021 | NULO | NULO | 005 |
respuesta correcta:
4 - 2 = 2
¡Gracias por su ayuda!
Solved! Go to 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
)
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
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
@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
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
1 |