Problema con función SWITCH en campo calculado
Solicito su ayuda para resolver el siguiente escenario que estoy enfrentando: Estoy trabajando con una tabla de hechos que contiene los siguientes campos: Id_Transaccion Id_MedioPago MedioPago_Ezypass A continuación, presento un ejemplo de los registros involucrados: Id_MedioPago MedioPago_Ezypass 24 1 25 1 Mi intención es generar dos nuevos campos calculados llamados: FormaPago MedioPago En estos campos, quiero aplicar una lógica que: 1. Muestre los medios 24 y 25 de forma individual, es decir: 24 como "WOMPI NEQUI RECURRENTE" 25 como "WOMPI TC RECURRENTE" 2. Agregue un nuevo medio adicional llamado "EZYPASS", que represente la combinación de los medios 24 y 25, únicamente cuando el campo MedioPago_Ezypass = 1 El inconveniente es que, al implementar esta lógica con la función SWITCH en un campo calculado, solo se muestra una de las condiciones. Por ejemplo: Si se evalúa primero la lógica de "EZYPASS", solo se muestra "EZYPASS" y no los medios 24 y 25 individualmente. Y si se prioriza mostrar los medios individuales, entonces no se muestra "EZYPASS". Es decir, no se pueden visualizar los tres valores simultáneamente en un gráfico o tabla dinámica de Power BI. Adjunto la tabla puente que contiene la lógica de agrupación deseada para que tengan el contexto completo de cómo deben visualizarse los datos. Campo calculado MedioPagoAgrupado = VAR _IdMedioPago = FACT_Pagos[FACT Pagos.Id Medio Pago 1] VAR _FormaPago = SWITCH( TRUE(), _IdMedioPago IN {24, 25, 18}, "EZYPAY", _IdMedioPago IN {26, 21, 16, 7}, "FLUJO AUTOMÁTICO", _IdMedioPago IN {14, 19}, "PAGO DIRECTO", _IdMedioPago = 20, "SMARTPAY", _IdMedioPago IN {2, 3, 4, 6}, "POS", BLANK() ) -- GOPASS se trata diferente VAR _TipoGopass = IF( _IdMedioPago = 26, IF(FACT_Pagos[FACT Pagos.Antena] = "TRUE", "GOPASS TAGS", "GOPASS PLACAS"), BLANK() ) -- Obtener nombre real (puede ser Wompi, Ezypass, etc.) VAR _NombreMedio = LOOKUPVALUE( Tabla_Puente_Medios_Formas[MedioPagoAgrupado], Tabla_Puente_Medios_Formas[Id_MedioPago], _IdMedioPago, Tabla_Puente_Medios_Formas[FormaPago], _FormaPago ) -- Formar clave compuesta VAR _Clave = _NombreMedio & "|" & _FormaPago & "|" & FORMAT(_IdMedioPago, "000") RETURN IF( NOT ISBLANK(_TipoGopass), _TipoGopass, LOOKUPVALUE( Tabla_Puente_Medios_Formas[MedioPagoAgrupado], Tabla_Puente_Medios_Formas[ClaveCompuesta], _Clave ) ) Tabla puente Tabla_Puente_Medios_Formas = DATATABLE( "MedioPagoAgrupado", STRING, "FormaPago", STRING, "Id_MedioPago", INTEGER, "ClaveCompuesta", STRING, { -- EZYPAY {"WOMPI NEQUI RECURRENTE", "EZYPAY", 24, "WOMPI NEQUI RECURRENTE|EZYPAY|024"}, {"WOMPI TC RECURRENTE", "EZYPAY", 25, "WOMPI TC RECURRENTE|EZYPAY|025"}, {"DAVIPLATA", "EZYPAY", 18, "DAVIPLATA|EZYPAY|018"}, -- FLUJO AUTOMÁTICO {"EZYPASS", "FLUJO AUTOMÁTICO", 24, "EZYPASS|FLUJO AUTOMÁTICO|024"}, {"EZYPASS", "FLUJO AUTOMÁTICO", 25, "EZYPASS|FLUJO AUTOMÁTICO|025"}, {"CARROYA", "FLUJO AUTOMÁTICO", 21, "CARROYA|FLUJO AUTOMÁTICO|021"}, {"COPILOTO", "FLUJO AUTOMÁTICO", 16, "COPILOTO|FLUJO AUTOMÁTICO|016"}, {"FLYPASS", "FLUJO AUTOMÁTICO", 7, "FLYPASS|FLUJO AUTOMÁTICO|007"}, -- GOPASS {"GOPASS TAGS", "FLUJO AUTOMÁTICO", 26, "GOPASS TAGS|FLUJO AUTOMÁTICO|026"}, {"GOPASS PLACAS", "FLUJO AUTOMÁTICO", 26, "GOPASS PLACAS|FLUJO AUTOMÁTICO|026"}, -- PAGO DIRECTO {"QR BANCOLOMBIA", "PAGO DIRECTO", 14, "QR BANCOLOMBIA|PAGO DIRECTO|014"}, {"QR ESTATICO BANCOLOMBIA", "PAGO DIRECTO", 19, "QR ESTATICO BANCOLOMBIA|PAGO DIRECTO|019"}, -- SMARTPAY {"EFECTIVO", "SMARTPAY", 20, "EFECTIVO|SMARTPAY|020"}, -- POS (Pagos Payment) {"EFECTIVO", "POS", 2, "EFECTIVO|POS|002"}, {"TARJETA DEBITO", "POS", 3, "TARJETA DEBITO|POS|003"}, {"TARJETA CREDITO", "POS", 4, "TARJETA CREDITO|POS|004"}, {"ELECTRONICO", "POS", 6, "ELECTRONICO|POS|006"} } )
Get the average of a column value based on total count of values in ID column
Hi, In a table i have column called "Object" which runs multiple times in a day and that runtime is captured in "StartTime" & "EndTime" columns, and the difference between the start and end time is captured in the "Duration" Column. Each object will be having multiple runid's which is captured in "RunID" column which is a unique value. For eg: Object AAA has two runid's called "111,222" and Object BBB has one runid called "333". Now i would like to get the average of each duration by count of that particular runid which is captured in "New Duration" column. For eg: The count of 111 runid is '10'. so for all those durations with runid 111 should be divided by the 10. The count of 222 runid is '6'. so for all those durations with runid 222 should be divided by the 6. The count of 333 runid is '8'. so for all those durations with runid 333 should be divided by the 8. Sample screenshot for above query: So since my Duration is "10" and total count of runid (111) is 10. So 10/10=1 that is my newduration.Similarly for all durations with runid's 111 should be divided bt 10. How to achieve this using DAX query?? I am connecting to this table in powerbi via SQL direct query mode. Thanks.
Adding values on the same row from different columns
Hey guys, I'm just wondering if anyone can help me to solve what seems like a simple addition problem. I currently have two columns: - status update values (1-3) - Objective values (4-10) Ideally, in a new column, I would like to add both sets of values on the same row (i.e. Status update value + Objective values = total score) Would anyone happen to know the DAX formula for such an equation? Any help is much appreciated! Cheers, BGet lookup values based on multiple conditions
Hello, I have an excel file with two sheets called Data and MF. S.NONew : =IF(OR(D2="#";D2="NULL");C2;D2) CombinedValue: = B2&A2&E2 (concatenation of ID ,IDNew, S.NONew) Rep: = IFERROR(IF(OR(G2="BLANK";G2="CN";G2="PERSON OR COMPANY'S NAME");VLOOKUP(B2;MF!A:B;2;FALSE);VLOOKUP(A2;MF!A:B;2;FALSE));"UNASSIGNED") I want to implement the S.NoNew, CombinedValue and Rep columns in Power BI desktop instead of in Excel. I imported the data into Power BI and i created S.NoNew and CombinedValue columns but it would be great if someone can help in creating the Rep column in Power BI. Regards, Krishna.
Is possible to show One Metric with details and rest simple by using Dax functions ?
Hello, I need to show measures dynamically in Grid or Matrix visual, I have a set of data with over 20+ Metrics precalculate in SQL. Those measures come from different Fact tables and it is with tabular data. Based on from audience request I am looking to see if can be possible to do this: Using Dynamic Slicer show in the front one measure with some extra analisis (frame in blue) then keep showing the rest of the other measures simple as thier value come. I am be able to change dynamically the blue frame but I wondering if it is possible to show the rest of the measures. I am looking to do it in DAX but let please know if this can be done under Dax or under Query Editor but my preference is under dax. For example from the slicer metric selection I choose metric A. This one show all the details related variance, vol, vol% , impact, etc. but then rest of the metrics not select show next the metric selected it. Then if Metric B is select show the details and rest of the metrics simple.(metric b is back wit rest of the metrics) If the Metric c is select the that change in the blue frame and now metric B and metric A are back wit the rest of the metrics Apprecite your advice and guide.
Help for Maxx dax function error
Hi, I am trying to get Max of fee rate for each stream. I want to get the result as desired max fee highligted in yellow as shown in the table below. I tried this dax calculation: Desired max fee = MAXX(VALUES('Session Budget Tracker'[Stream]),CALCULATE(MAX('Session Budget Tracker'[Session Fee ($)]),ALLEXCEPT('Session Budget Tracker','Session Budget Tracker'[Stream]))) Can anyone help me to get the correct values please. Kind Regards, Thilinigenerate dax function running very slow , could you please help me to improve
HI Master of DAX , I am facing issue with below code running long time could you please help me to imporve the it VAR Table = FILTER( ADDCOLUMNS( GENERATE( ALLSELECTED(Cust[Name]) , ALLSELECTED(CESM[Metric], CESM[MType]) ) , "Measure Value" , ),NOT(ISBlank([Measure Value]) Thanks in AdvanceCounting ELA and MATH
I have an issue and am having trouble wrapping my head around it. I am trying to count students absent in English and Math classes. The issue here is the database places students absent per period on a row to row basis. I have created a sample here. The original one is over a million rows. I was wondering if there is any way I can create a column that counts number of students absent in MATH and ELA. I know I can do table. But I need to be looking more like the picture below (red font is where I want the calculation) and was wondering if someone could help.
