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.
Estimados expertos,
Estoy tratando de crear una medida o columna calculada que logre la columna "Fee (resultado deseado) en la tabla siguiente aquí:
Tengo hasta ahora:
Column =
CALCULATE(MAX('Fee Rules'[Fee]);
FILTER('Fee Rules';
'Fee Rules'[Customer] = Case_table[Customer]
&& 'Fee Rules'[Case Type] = Case_table[Case_type]
&& 'Fee Rules'[Cover] = Case_table[Cover]))
Sin embargo, ahora no puedo implementar una lógica para "Más que" y "Menos que".
¿Alguien podría ayudar, por favor?
Solved! Go to Solution.
Hola @setis ,
Podemos intentar crear una columna calucada usando la siguiente fórmula para cumplir con sus requisitos:
Fee (desired result) =
VAR t_customer =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] ),
FILTER (
'Rules',
'Rules'[Case_Type] = 'Case table'[Case_Type]
&& 'Rules'[Cover] = 'Case table'[Cover]
&& 'Rules'[Customer] = 'Case Table'[Customer_A]
)
)
VAR action_A =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] ),
FILTER (
'Rules',
OR (
AND (
'Rules'[Nr_action_a] = "Less than",
'Case table'[Nr_action_A] < 'Rules'[ft_number]
),
AND (
'Rules'[Nr_action_A] = "More than",
'Case table'[Nr_action_A] > 'Rules'[ft_number]
)
)
)
)
VAR action_B =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] ),
FILTER (
'Rules',
OR (
AND (
'Rules'[Nr_action_B] = "Less than",
'Case table'[Nr_action_B] < 'Rules'[ft_number]
),
AND (
'Rules'[Nr_action_B] = "More than",
'Case table'[Nr_action_B] > 'Rules'[ft_number]
)
)
)
)
RETURN
CALCULATE (
MAX ( 'Rules'[Fee] ),
FILTER (
'Rules',
'Rules'[Fee] IN t_customer
&& 'Rules'[Fee] IN action_A
&& 'Rules'[Fee] IN action_B
)
)
Por cierto, pbIX archivo como adjunto.
Saludos
Hola @setis ,
Podemos utilizar la siguiente medida para cumplir con sus requisitos:
Fee =
VAR t_customer =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] ),
FILTER (
'Rules',
'Rules'[Case_Type] = 'Case table'[Case_Type]
&& 'Rules'[Cover] = 'Case table'[Cover]
&& 'Rules'[Customer] = 'Case Table'[Customer_A]
)
)
VAR action_A =
CALCULATETABLE (
DISTINCT ( Rules[Fee] ),
FILTER (
'Rules',
AND (
'Rules'[Fee] IN t_customer,
AND (
'Rules'[Nr_action_A] = "Less than",
'Case Table'[Nr_action_A] < Rules[ft_number]
)
|| AND (
'Rules'[Nr_action_A] = "More than",
'Case Table'[Nr_action_A] > Rules[ft_number]
)
|| AND ( 'Rules'[Nr_action_A] & "" = "", ISBLANK ( 'Case Table'[Nr_action_A] ) )
)
)
)
VAR action_B =
CALCULATETABLE (
DISTINCT ( Rules[Fee] ),
FILTER (
'Rules',
AND (
'Rules'[Fee] IN t_customer,
AND (
'Rules'[Nr_action_B] = "Less than",
'Case Table'[Nr_action_B] < Rules[ft_number]
)
|| AND (
'Rules'[Nr_action_B] = "More than",
'Case Table'[Nr_action_B] > Rules[ft_number]
)
|| AND ( 'Rules'[Nr_action_B] & "" = "", ISBLANK ( 'Case Table'[Nr_action_B] ) )
)
)
)
VAR temp =
FILTER (
SUMMARIZE (
FILTER ( 'Rules', 'Rules'[Fee] IN t_customer ),
Rules[Fee],
"RulesNumber", COUNTROWS ( Rules ),
"TotalAmount", SUM ( Rules[Amount] )
),
IF (
[RulesNumber] = 1,
'Rules'[Fee] IN action_A
|| 'Rules'[Fee] IN action_B,
'Rules'[Fee] IN action_A
&& 'Rules'[Fee] IN action_B
)
)
RETURN
MAXX ( TOPN ( 1, temp, [TotalAmount], DESC ), [Fee] )
Por cierto, pbIX archivo como adjunto.
Saludos
@setis por favor explique la lógica, no está completamente claro para mí lo que está tratando de lograr.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k, lo siento si no fui lo suficientemente claro.
Estoy intentando crear una columna en mi tabla Caso (la tabla siguiente en la captura de pantalla) que identifica la Tarifa que el caso debe tener basada en la tabla Reglas de tarifas (la tabla anterior).
El caso1 tiene <1 Nr_action_A y <5 Nr_Action_B, por lo tanto, su tasa es "Fee_Gold"
¿Tiene sentido?
@setis cuáles son las columnas que estamos comprobando en la tabla de reglas.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Todos excepto la cantidad.
Como se puede ver en la medida:
Column =
CALCULATE(MAX('Fee Rules'[Fee]);
FILTER('Fee Rules';
'Fee Rules'[Customer] = Case_table[Customer]
&& 'Fee Rules'[Case Type] = Case_table[Case_type]
&& 'Fee Rules'[Cover] = Case_table[Cover]))
El Cliente, el Tipo de Caso y la Cubierta deben ser los mismos. El problema es Nr_Action_A y Nr_Action_B que es "Más que" o "Menos que" el valor de la columna "ft_number".
@parry2k ,
Como un intento de hacerlo más claro, agregué un par de columnas calculadas:
Como puede ver lo que estoy buscando en la columna de resultados deseada es algo así como: Compruebe el Nr_action_A y necesito filtrar las reglas de tarifapara las filas que coinciden con Nr_action_A con el Action_A_operator y Nr_actionA de las reglas de tarifa. Para el caso 1 Nr-action_A 0 por lo que devolvería de las reglas de tarifa la segunda fila que es la Acción A - Menos de 1.
¿Tiene sentido? ¿Es posible?
Hola @setis ,
Podemos intentar crear una columna calucada usando la siguiente fórmula para cumplir con sus requisitos:
Fee (desired result) =
VAR t_customer =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] ),
FILTER (
'Rules',
'Rules'[Case_Type] = 'Case table'[Case_Type]
&& 'Rules'[Cover] = 'Case table'[Cover]
&& 'Rules'[Customer] = 'Case Table'[Customer_A]
)
)
VAR action_A =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] ),
FILTER (
'Rules',
OR (
AND (
'Rules'[Nr_action_a] = "Less than",
'Case table'[Nr_action_A] < 'Rules'[ft_number]
),
AND (
'Rules'[Nr_action_A] = "More than",
'Case table'[Nr_action_A] > 'Rules'[ft_number]
)
)
)
)
VAR action_B =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] ),
FILTER (
'Rules',
OR (
AND (
'Rules'[Nr_action_B] = "Less than",
'Case table'[Nr_action_B] < 'Rules'[ft_number]
),
AND (
'Rules'[Nr_action_B] = "More than",
'Case table'[Nr_action_B] > 'Rules'[ft_number]
)
)
)
)
RETURN
CALCULATE (
MAX ( 'Rules'[Fee] ),
FILTER (
'Rules',
'Rules'[Fee] IN t_customer
&& 'Rules'[Fee] IN action_A
&& 'Rules'[Fee] IN action_B
)
)
Por cierto, pbIX archivo como adjunto.
Saludos
Estimado @v-lid-msft , gracias de nuevo por su ayuda hasta ahora
La lógica de la columna calcular no funciona cuando hay espacios en blanco.
He añadido a su archivo un nuevo caso y 2 reglas que producen resultados incorrectos en una columna calculada "Fee2" que creé tratando de tener en cuenta los espacios en blanco.
¿Podrías investigarlo, por favor?
https://drive.google.com/file/d/1ZHID2gptlonyyA83albNwiGHxnDueRy2/view?usp=sharing
PS. ¿Cómo adjunto un archivo a las publicaciones?
Hola @setis ,
Hemos encontrado nuestro error en la fórmula, por favor trate de utilizar la siguiente medida y verificar si la salida es adecuada:
Fee (desired result) =
VAR t_customer =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] ),
FILTER (
'Rules',
'Rules'[Case_Type] = 'Case table'[Case_Type]
&& 'Rules'[Cover] = 'Case table'[Cover]
&& 'Rules'[Customer] = 'Case Table'[Customer_A]
)
)
VAR action_A =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] ),
FILTER (
'Rules',
OR (
AND (
'Rules'[Nr_action_a] = "Less than",
'Case table'[Nr_action_A] < 'Rules'[ft_number]
),
AND (
'Rules'[Nr_action_A] = "More than",
'Case table'[Nr_action_A] > 'Rules'[ft_number]
)
)
)
)
VAR action_B =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] ),
FILTER (
'Rules',
OR (
AND (
'Rules'[Nr_action_B] = "Less than",
'Case table'[Nr_action_B] < 'Rules'[ft_number]
),
AND (
'Rules'[Nr_action_B] = "More than",
'Case table'[Nr_action_B] > 'Rules'[ft_number]
)
)
)
)
RETURN
CALCULATE (
MAX ( 'Rules'[Fee] ),
FILTER (
'Rules',
'Rules'[Fee] IN t_customer
&& 'Rules'[Fee] IN action_A
&& 'Rules'[Fee] IN action_B
)
)
Por cierto, pbIX archivo como adjunto.
Saludos
Estimado @v-lid-msft ,
Muchas gracias. ¡Esto es genial!
Con la información en el ejemplo, funciona perfectamente. Sin embargo, en mi informe de la vida real, puede haber casos cuyas condiciones coincidan con las reglas de 2 o más tarifas.
Como el caso 4 aquí:
Casos:
Reglas:
¿Cómo puedo implementar la lógica en la columna que cuando esto sucede, tiene que elegir la tarifa con la cantidad más alta?
Intenté agregar un segundo filtro al final, pero no funcionó:
Fee =
VAR t_customer =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] );
FILTER (
'Rules';
'Rules'[Case_Type] = 'Case table'[Case_Type]
&& 'Rules'[Cover] = 'Case table'[Cover]
&& 'Rules'[Customer] = 'Case Table'[Customer_A]
)
)
VAR action_A =
CALCULATETABLE (
DISTINCT ( Rules[Fee] );
FILTER (
'Rules';
AND (
'Rules'[Nr_action_A] = "Less than";
'Case Table'[Nr_action_A] < Rules[ft_number]
)
|| AND (
'Rules'[Nr_action_A] = "More than";
'Case Table'[Nr_action_A] > Rules[ft_number]
)
|| AND ('Rules'[Nr_action_A] & "" = ""; ISBLANK('Case Table'[Nr_action_A] ))
)
)
VAR action_B =
CALCULATETABLE (
DISTINCT ( Rules[Fee] );
FILTER (
'Rules';
AND (
'Rules'[Nr_action_B] = "Less than";
'Case Table'[Nr_action_B] < Rules[ft_number]
)
|| AND (
'Rules'[Nr_action_B] = "More than";
'Case Table'[Nr_action_B] > Rules[ft_number]
)
||AND ( 'Rules'[Nr_action_B] & "" = ""; ISBLANK('Case Table'[Nr_action_B] ))
)
)
RETURN
CALCULATE (
MAX ( 'Rules'[Fee] );
FILTER (
'Rules';
'Rules'[Fee] IN t_customer
&& 'Rules'[Fee] IN action_A
&& 'Rules'[Fee] IN action_B
);
FILTER(Rules;MAX(Rules[Amount]))
)
El archivo está aquí: https://drive.google.com/file/d/1ZHID2gptlonyyA83albNwiGHxnDueRy2/view?usp=sharing
¡Gracias de antemano!
Hola @setis ,
Podemos utilizar la siguiente medida para cumplir con sus requisitos:
Fee =
VAR t_customer =
CALCULATETABLE (
DISTINCT ( 'Rules'[Fee] ),
FILTER (
'Rules',
'Rules'[Case_Type] = 'Case table'[Case_Type]
&& 'Rules'[Cover] = 'Case table'[Cover]
&& 'Rules'[Customer] = 'Case Table'[Customer_A]
)
)
VAR action_A =
CALCULATETABLE (
DISTINCT ( Rules[Fee] ),
FILTER (
'Rules',
AND (
'Rules'[Fee] IN t_customer,
AND (
'Rules'[Nr_action_A] = "Less than",
'Case Table'[Nr_action_A] < Rules[ft_number]
)
|| AND (
'Rules'[Nr_action_A] = "More than",
'Case Table'[Nr_action_A] > Rules[ft_number]
)
|| AND ( 'Rules'[Nr_action_A] & "" = "", ISBLANK ( 'Case Table'[Nr_action_A] ) )
)
)
)
VAR action_B =
CALCULATETABLE (
DISTINCT ( Rules[Fee] ),
FILTER (
'Rules',
AND (
'Rules'[Fee] IN t_customer,
AND (
'Rules'[Nr_action_B] = "Less than",
'Case Table'[Nr_action_B] < Rules[ft_number]
)
|| AND (
'Rules'[Nr_action_B] = "More than",
'Case Table'[Nr_action_B] > Rules[ft_number]
)
|| AND ( 'Rules'[Nr_action_B] & "" = "", ISBLANK ( 'Case Table'[Nr_action_B] ) )
)
)
)
VAR temp =
FILTER (
SUMMARIZE (
FILTER ( 'Rules', 'Rules'[Fee] IN t_customer ),
Rules[Fee],
"RulesNumber", COUNTROWS ( Rules ),
"TotalAmount", SUM ( Rules[Amount] )
),
IF (
[RulesNumber] = 1,
'Rules'[Fee] IN action_A
|| 'Rules'[Fee] IN action_B,
'Rules'[Fee] IN action_A
&& 'Rules'[Fee] IN action_B
)
)
RETURN
MAXX ( TOPN ( 1, temp, [TotalAmount], DESC ), [Fee] )
Por cierto, pbIX archivo como adjunto.
Saludos
@v-lid-msft Parece estar funcionando. No puedo agradecerte lo suficiente por tu ayuda aquí 🙂
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 |