Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hola
He estado tratando de crear un código dax que reagruparía mis datos como se muestra a continuación:
Id | Estado | Freq | Id | Estado | |
101173150 | Obediente | 1 | 101173150 | Obediente | |
101173547 | Obediente | 2 | 101173547 | Obediente | |
101173547 | Primero | 2 | 101173547 | Obediente | |
101173549 | Obediente | 2 | 101173549 | No cumple | |
101173549 | No cumple | 2 | 101173549 | No cumple | |
101173571 | No cumple | 1 | 101173571 | No cumple | |
101173572 | No cumple | 2 | 101173572 | No cumple | |
101173572 | Última | 2 | 101173572 | No cumple | |
101173702 | Obediente | 1 | 101173702 | Obediente | |
101173706 | Obediente | 2 | 101173706 | No cumple | |
101173706 | No cumple | 2 | 101173706 | No cumple | |
101173815 | Obediente | 2 | 101173815 | No cumple | |
101173815 | No cumple | 2 | 101173815 | No cumple | |
101173847 | No cumple | 1 | 101173847 | No cumple | |
101173868 | Obediente | 3 | 101173868 | No cumple | |
101173868 | No cumple | 3 | 101173868 | No cumple | |
101173868 | Primero | 3 | 101173868 | No cumple |
El escenario es que cuando hay varios valores para un ID priorizaría el estado "no conforme" si "no es compatible" no está allí, tomaría "conforme" sobre el estado "primero" o "último".
Solved! Go to Solution.
No @ladyhaley ,
Hay un pequeño error en la expresión proporcionada por @amitchandak. Por favor, modifíquelo de la siguiente manera:
New column =
VAR _nc =
COUNTX (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Not Compliant" ),
[ID]
)
VAR _c =
COUNTX (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Compliant" ),
[ID]
)
VAR _f =
COUNTX (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "First" ),
[ID]
)
RETURN
SWITCH (
TRUE (),
NOT ( ISBLANK ( _nc ) ), "Not Compliant",
NOT ( ISBLANK ( _c ) ), "Compliant",
NOT ( ISBLANK ( _f ) ), "First",
"Last"
)
O prueba esto:
Column =
VAR _nc =
COUNTROWS (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Not Compliant" )
)
VAR _c =
COUNTROWS (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Compliant" )
)
VAR _f =
COUNTROWS (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "First" ) )
RETURN
SWITCH (
TRUE (),
NOT ( ISBLANK ( _nc ) ), "Not Compliant",
NOT ( ISBLANK ( _c ) ), "Compliant",
NOT ( ISBLANK ( _f ) ), "First",
"Last"
)
Measure =
VAR _nc =
COUNTROWS (
FILTER (
ALLSELECTED ( 'Table' ),
[ID] = MAX ( [ID] )
&& [Status] = "Not Compliant"
)
)
VAR _c =
COUNTROWS (
FILTER (
ALLSELECTED ( 'Table' ),
[ID] = MAX ( [ID] )
&& [Status] = "Compliant"
)
)
VAR _f =
COUNTROWS (
FILTER ( ALLSELECTED ( 'Table' ), [ID] = MAX ( [ID] ) && [Status] = "First" )
)
RETURN
SWITCH (
TRUE (),
NOT ( ISBLANK ( _nc ) ), "Not Compliant",
NOT ( ISBLANK ( _c ) ), "Compliant",
NOT ( ISBLANK ( _f ) ), "First",
"Last"
)
Saludos
Icey
Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
No @ladyhaley ,
Hay un pequeño error en la expresión proporcionada por @amitchandak. Por favor, modifíquelo de la siguiente manera:
New column =
VAR _nc =
COUNTX (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Not Compliant" ),
[ID]
)
VAR _c =
COUNTX (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Compliant" ),
[ID]
)
VAR _f =
COUNTX (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "First" ),
[ID]
)
RETURN
SWITCH (
TRUE (),
NOT ( ISBLANK ( _nc ) ), "Not Compliant",
NOT ( ISBLANK ( _c ) ), "Compliant",
NOT ( ISBLANK ( _f ) ), "First",
"Last"
)
O prueba esto:
Column =
VAR _nc =
COUNTROWS (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Not Compliant" )
)
VAR _c =
COUNTROWS (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "Compliant" )
)
VAR _f =
COUNTROWS (
FILTER ( 'Table', [ID] = EARLIER ( [ID] ) && [Status] = "First" ) )
RETURN
SWITCH (
TRUE (),
NOT ( ISBLANK ( _nc ) ), "Not Compliant",
NOT ( ISBLANK ( _c ) ), "Compliant",
NOT ( ISBLANK ( _f ) ), "First",
"Last"
)
Measure =
VAR _nc =
COUNTROWS (
FILTER (
ALLSELECTED ( 'Table' ),
[ID] = MAX ( [ID] )
&& [Status] = "Not Compliant"
)
)
VAR _c =
COUNTROWS (
FILTER (
ALLSELECTED ( 'Table' ),
[ID] = MAX ( [ID] )
&& [Status] = "Compliant"
)
)
VAR _f =
COUNTROWS (
FILTER ( ALLSELECTED ( 'Table' ), [ID] = MAX ( [ID] ) && [Status] = "First" )
)
RETURN
SWITCH (
TRUE (),
NOT ( ISBLANK ( _nc ) ), "Not Compliant",
NOT ( ISBLANK ( _c ) ), "Compliant",
NOT ( ISBLANK ( _f ) ), "First",
"Last"
)
Saludos
Icey
Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
@ladyhaley , una nueva columna como
Nueva columna ?
var _nc - countx(filter(table, [ID] ?earlier([ID]), [Status] ?"Not Compliant"),[ID])
var _c - countx(filter(table, [ID] ?earlier([ID]), [Status] ?"Compliant"),[ID])
var _f - countx(filter(table, [ID] ?earlier([ID]), [Status] ?"First"),[ID])
devolución
Switch( true(),
not(isblank(_nc)) , "Not Compliant",
not(isblank(_c)) , "Compliant",
not(isblank(_f)) , "First",
"El último"
)