Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hola a tod@s,
A ver si alguien me puede dar una solución.
Tengo un tabla con estos campos:
Nombre | Producto | ID |
ACC001 | Capso 2021 | 3 |
ACC002 | Capso 2021 | 3 |
ACC003 | Capso 2020 | 2 |
ACC004 | Capso 2020 | 2 |
ACC004 | Capso 2019 | 1 |
Como se observa el nombre "ACC004" se repite porque tiene 2 productos "Capso 2020" y "Capso 2019".
Tengo otra tabla que es un listado único de nombres creado con un "DISTINC(SELECTCOLUMNS)".
A esta última tabla me quiero llevar, relacionando con el nombre, el producto. En caso que haya dos nombre con productos distintos quiero que me lleve el que tenga el ID más alto. En este caso que me lleve de "ACC04" el producto "Capso 2020".
He probado con lookupvalue y alguna otra variable pero no me trae el campo correcto.
¿alguna idea por favor?
Un saludo y gracias de antemano
Solved! Go to Solution.
I suppose your tables are connected via Nombre column:
I've created 'Tabla2' via Tabla2 = DISTINCT(Tabla1[Nombre]).
In case of calculated columns:
CAPSO =
VAR currNombre = Tabla2[Nombre]
VAR _tC =
CALCULATETABLE (
Tabla1,
Tabla1[Nombre] = currNombre
&& CONTAINSSTRING ( Tabla1[Producto], "CAPSO" )
)
VAR maxCID = MAXX ( _tC, Tabla1[ID CAPSO] )
RETURN
CALCULATE ( MAX ( Tabla1[Producto] ), FILTER ( _tC, [ID CAPSO] = maxCID ) )
PRICCOA =
VAR currNombre = Tabla2[Nombre]
VAR _tP =
CALCULATETABLE (
Tabla1,
Tabla1[Nombre] = currNombre
&& CONTAINSSTRING ( Tabla1[Producto], "PRICCOA" )
)
VAR maxPID = MAXX ( _tP, Tabla1[ID PRICCOA] )
RETURN
CALCULATE ( MAX ( Tabla1[Producto] ), FILTER ( _tP, [ID PRICCOA] = maxPID ) )
In case of measures:
#CAPSO =
VAR currNombre = MAX(Tabla2[Nombre])
VAR _tC =
CALCULATETABLE (
Tabla1,
Tabla1[Nombre] = currNombre
&& CONTAINSSTRING ( Tabla1[Producto], "CAPSO" )
)
VAR maxCID = MAXX ( _tC, Tabla1[ID CAPSO] )
RETURN
IF (
HASONEVALUE ( Tabla2[Nombre] ),
COALESCE (
CALCULATE ( MAX ( Tabla1[Producto] ), FILTER ( _tC, [ID CAPSO] = maxCID ) ),
""
)
)
#PRICCOA =
VAR currNombre = MAX(Tabla2[Nombre])
VAR _tP =
CALCULATETABLE (
Tabla1,
Tabla1[Nombre] = currNombre
&& CONTAINSSTRING ( Tabla1[Producto], "PRICCOA" )
)
VAR maxPID = MAXX ( _tP, Tabla1[ID PRICCOA] )
RETURN
IF (
HASONEVALUE ( Tabla2[Nombre] ),
COALESCE (
CALCULATE ( MAX ( Tabla1[Producto] ), FILTER ( _tP, [ID PRICCOA] = maxPID ) ),
""
)
)
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @jemalosa ,
Please, show the second table and expected result.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hola,
Te muestro un ejemplo más realista de la tabla que tengo (tabla1)y de la tabla esperada (tabla2):
Tabla1:
Nombre Producto ID CAPSO ID PRICCOA
AC001 CAPSO 2019 6 0
AC001 CAPSO 2003 3 0
AC001 PRICCOA 2003 0 1
AC002 CAPSO 2003 3 0
AC003 CAPSO 2019 6 0
AC003 PRICCOA 2020 0 11
AC004 CAPSO 2019 6 0
AC005 CAPSO 2019 6 0
AC006 CAPSO 2019 6 0
AC007 CAPSO 2019 6 0
AC008 CAPSO 2020 7 0
AC009 CAPSO 2019 6 0
AC010 CAPSO 2019 6 0
AC010 CAPSO 2017 5 0
AC010 PRICCOA 2019 0 10
AC010 PRICCOA 2009 0 7
AC011 CAPSO 2003 3 0
AC012 CAPSO 2003 3 0
AC013 CAPSO 2003 3 0
AC014 CAPSO 2003 3 0
AC015 CAPSO 2003 3 0
AC016 CAPSO 2003 3 0
AC017 CAPSO 2019 6 0
AC017 CAPSO 2003 3 0
AC019 PRICCOA 2020 0 11
AC019 PRICCOA 2009 0 7
AC021 PRICCOA 2020 0 11
AC023 PRICCOA 2020 0 11
AC023 PRICCOA 2003 0 1
AC024 PRICCOA 2003 0 1
AC025 PRICCOA 2019 0 10
AC026 PRICCOA 2019 0 10
AC027 PRICCOA 2009 0 7
AC027 PRICCOA 2003 0 1
AC028 PRICCOA 2003 0 1
AC032 PRICCOA 2009 0 7
AC032 PRICCOA 2020 0 11
Tabla 2 (esperada):
Como verás, en la tabla 2 se muestran el mayor ID por producto
Nombre CAPSO PRICCOA
AC001 CAPSO 2019 PRICCOA 2003
AC002 CAPSO 2003
AC003 CAPSO 2019 PRICCOA 2020
AC004 CAPSO 2019
AC005 CAPSO 2019
AC006 CAPSO 2019
AC007 CAPSO 2019
AC008 CAPSO 2020
AC009 CAPSO 2019
AC010 CAPSO 2019 PRICCOA 2019
AC011 CAPSO 2003
AC012 CAPSO 2003
AC013 CAPSO 2003
AC014 CAPSO 2003
AC015 CAPSO 2003
AC016 CAPSO 2003
AC017 CAPSO 2019 CAPSO 2019
AC019
PRICCOA 2020
AC021
PRICCOA 2020
AC023
PRICCOA 2020
AC024
PRICCOA 2003
AC025
PRICCOA 2019
AC026
PRICCOA 2019
AC027
PRICCOA 2009
AC028
PRICCOA 2003
AC032
PRICCOA 2020
Un saludo y gracias por la ayuda
I find it a bit difficult to define which data should go to which column. Please, share a screenshot of the table (just couple of rows with column names). Thanks.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
I suppose your tables are connected via Nombre column:
I've created 'Tabla2' via Tabla2 = DISTINCT(Tabla1[Nombre]).
In case of calculated columns:
CAPSO =
VAR currNombre = Tabla2[Nombre]
VAR _tC =
CALCULATETABLE (
Tabla1,
Tabla1[Nombre] = currNombre
&& CONTAINSSTRING ( Tabla1[Producto], "CAPSO" )
)
VAR maxCID = MAXX ( _tC, Tabla1[ID CAPSO] )
RETURN
CALCULATE ( MAX ( Tabla1[Producto] ), FILTER ( _tC, [ID CAPSO] = maxCID ) )
PRICCOA =
VAR currNombre = Tabla2[Nombre]
VAR _tP =
CALCULATETABLE (
Tabla1,
Tabla1[Nombre] = currNombre
&& CONTAINSSTRING ( Tabla1[Producto], "PRICCOA" )
)
VAR maxPID = MAXX ( _tP, Tabla1[ID PRICCOA] )
RETURN
CALCULATE ( MAX ( Tabla1[Producto] ), FILTER ( _tP, [ID PRICCOA] = maxPID ) )
In case of measures:
#CAPSO =
VAR currNombre = MAX(Tabla2[Nombre])
VAR _tC =
CALCULATETABLE (
Tabla1,
Tabla1[Nombre] = currNombre
&& CONTAINSSTRING ( Tabla1[Producto], "CAPSO" )
)
VAR maxCID = MAXX ( _tC, Tabla1[ID CAPSO] )
RETURN
IF (
HASONEVALUE ( Tabla2[Nombre] ),
COALESCE (
CALCULATE ( MAX ( Tabla1[Producto] ), FILTER ( _tC, [ID CAPSO] = maxCID ) ),
""
)
)
#PRICCOA =
VAR currNombre = MAX(Tabla2[Nombre])
VAR _tP =
CALCULATETABLE (
Tabla1,
Tabla1[Nombre] = currNombre
&& CONTAINSSTRING ( Tabla1[Producto], "PRICCOA" )
)
VAR maxPID = MAXX ( _tP, Tabla1[ID PRICCOA] )
RETURN
IF (
HASONEVALUE ( Tabla2[Nombre] ),
COALESCE (
CALCULATE ( MAX ( Tabla1[Producto] ), FILTER ( _tP, [ID PRICCOA] = maxPID ) ),
""
)
)
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
ERD, muchas gracias!!!!
Funcionó correctamente.
Un saludo
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
65 | |
62 | |
55 |
User | Count |
---|---|
172 | |
108 | |
105 | |
71 | |
70 |