"power bi"

62 Topics

Unproductive customer
Hi All, In my Power BI report, we have only demention columns (e.g Customer name , code and location) and las invoice date(taken max invoice date from sales fact table). in this report we want to show only unproductive customer, means they has not made any sales in the selected date range. Example: (if we select date range in the slicer, 1s Sept to 30th sept) Customer 001 status is delivered then they are productive customer and customer status is cancelled, returned the they're unproductive customer. want to show only unproductive customer. Tried creating DAX but it's not working. Can anyone please help?
Solved
Vidya_0505
9 months ago Place DAX Commands and Tips
1.2KViews
3likes
6Comments
How to do distinct count by time period with a DAX measure
I have a dimension table with column Account ID Account ID a b c and fact table linked by Account ID Merchant Account ID Date Value 1 a January 500 2 a February 100 2 b February 300 2 c January 200 3 a January 900 3 b February 400 I am looking to create a measure that would be used in a table that would show for each merchant, how many accounts have used it in a selected time period (e.g. YTD) so for February YTD it would show: Merchant Number of Accounts 1 1 2 3 3 2 but for January YTD it would show: Merchant Number of Accounts 1 1 2 1 3 1 I have thus far got the following: YTD count = CALCULATE(COUNTROWS(FILTER(VALUES(Dim[Account ID]),CALCULATE(SUM(Fact[Value])>0),ALL('Dates'),DATESYTD('Dates'[Date])) Unfortunately due to the high cardinality of the Merchant column in the dimension table and millions of records, it is exceeding the available resources. Please could I be guided on how to make the measure more efficient
Solved
aleary78
11 months ago Place DAX Commands and Tips
2.6KViews
1like
10Comments
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"} } )
Sandra05silva
1 year ago Place DAX Commands and Tips
871Views
0likes
4Comments
Need to return last datetime for each campaign_id from related table
Hi everyone, I’m trying to create a calculated column in my summary table called 'CampaignSummary' that returns the last event date for each CampaignID from a related events table called 'CampaignEvents'. Here’s the setup: 'CampaignSummary'[CampaignID] contains unique campaign IDs (one per row). 'CampaignEvents'[CampaignID] contains many events per campaign, each with an event [EventDate]. I want to return the last (most recent) EventDate from 'CampaignEvents' for each CampaignID. What I tried: LastEventDate = CALCULATE( MAX('CampaignEvents'[EventDate]), FILTER( 'CampaignEvents', 'CampaignEvents'[CampaignID] = 'CampaignSummary'[CampaignID] ) ) Issues: Some results return today’s date incorrectly. Others return blanks. I want the actual last event date for each campaign. EventDate is of data type datetime, and CampaignID exists in both tables as matching text values. I’m not sure if this needs to be done via TOPN, RANKX, or if Power Query would be a better option — any help would be appreciated. Thanks in advance! IF THIS WORKS BEST IN POWER QUERY EDITOR, I WILL DO THAT WAY EITHER.
Solved
Anonymous
1 year ago Place DAX Commands and Tips
3.3KViews
0likes
16Comments
Rolling sum of tickets in status with start and end date
Hello, I'm trying to create a rolling sum mesure that counts the aggregated number of issues that are in "Analysis" status on a monthly basis. I need to base this rolling sum on the status start date and end date. The end goal is to have a visual that looks similar to this one : At the beginning a big number of issues are in the analysis status (it is one of the first status in our workflow) and it decreases over time and inversely, the number of validated issues is low and increases over time. Here's the sample data (I'm only interested in Analysis (En Analyse) and Validated (Validée) statuses : https://we.tl/t-IXS2ysHXoM Thanks in advance for any suggestions. Regards, Iana
Solved
ianka08
1 year ago Place DAX Commands and Tips
832Views
0likes
4Comments
ToolTip Advance - Filter Help
Hi all, I'm working on a Power BI report where I show monthly Gross Sales in a table and would like to display a tooltip chart when hovering over the table rows. The goal of this tooltip is to show the entire year's trend line for Gross Sales, not just the single month that was hovered. What I want: When hovering over a row (e.g., Brand 3), I want the tooltip to show a Bar chart for the full year (e.g., Jan–Dec ). The tooltip should still respect other slicers from the main page: e.g., State but ignore Month Slicer What I’ve Tried: Created a Tooltip Page. Enabled the page as a tooltip and linked it to the table. It works — but only shows a single month (the row context from the table). However, the date context from the hovered row still dominates the chart axis — only one Bar appears instead of the full year. What I Need Help With: How can I structure my tooltip chart (and DAX) to: Ignore just the row-level month/date from the hovered row Still respect the full context of slicers (State etc.) Always show the full year trend on the tooltip chart? File working
Solved
dvraan
1 year ago Place DAX Commands and Tips
591Views
0likes
2Comments
Forecasted Inventory while staying above zero if negative
I currently have an issue with trying to forecast my stock on hand by SKU by month. I have my current SOH by SKU as well as forecasted sales & purchases which i have summarised into a net movements by month per SKU. The below tables are basic examples of my data and the 3rd table is a summary of how i want the calculations to work. Basically i need a running total of forecasted SOH where the SKU SOH for any given month returns 0 if the forecasted SOH is less than 0, but i would like the negative amount to carry over as open sales orders. So with SKU A, it returns 0 at 31/03/2025 because it is forecasted to be less than 0, but at 30/04/2025 it returns 7 because Opening Stock + Forecast Movements + Net units carried over = 0 + 10 - 3 = 7 Date SKU Stock on Hand 28/02/2025 A 2 28/02/2025 B 8 MonthYr SKU Net Forecast Movements 31/03/2025 A -5 30/04/2025 A 10 31/03/2025 B 1 30/04/2025 B 5 Date SKU Opening SOH Net Forecast Movements Forecasted SOH 31/03/2025 A 2 -5 0 31/03/2025 B 8 1 9 30/04/2025 A 0 10 7 30/04/2025 B 9 5 14
Solved
mmossy
1 year ago Place DAX Commands and Tips
757Views
0likes
4Comments
Using only DAX (not powerquery) drop duplicates based on a single column
I have a table similar to the following:- id Status Priority Created LastUpdated Resolved 12345 open high 10/03/2025 23:58 12/03/2025 23:58 12/03/2025 23:58 12346 open high 10/03/2025 23:58 12/03/2025 23:58 12/03/2025 23:58 12345 open high 08/03/2025 23:58 09/03/2025 23:58 N/I 12346 open low 07/03/2025 21:58 08/03/2025 23:58 N/I 23456 open low 06/03/2025 20:58 07/03/2025 23:58 N/I 31234 open med 01/03/2025 23:58 05/03/2025 23:58 05/03/2025 23:58 I need to remove any rows where there is a duplicate on the "id" column, keeping only the top/highest row in the table. The final table should be:- id Status Priority Created LastUpdated Resolved 12345 open high 10/03/2025 23:58 12/03/2025 23:58 12/03/2025 23:58 12346 open high 10/03/2025 23:58 12/03/2025 23:58 12/03/2025 23:58 23456 open low 06/03/2025 20:58 07/03/2025 23:58 N/I 31234 open med 01/03/2025 23:58 05/03/2025 23:58 05/03/2025 23:58 (Rows 4 & 5 have been removed as duplicates of rows 2 & 3) I've tried using DISTINCT (Table and Column versions) and RANKX but can't seem to achieve the results I need. Is this possible using only DAX in PowerBI?
Solved
famousmouse
1 year ago Place DAX Commands and Tips
676Views
0likes
1Comment
Show Task on Table Based on Selected Week
Hi, I am working on building table that show all tasks with Status "Not Start", "On Hold", "In Progress" and closed task (by Closed Date) based on selected week. Basically I have a project table, with structure like below: Task Created Date Start Date Closed Date Target Date Status A MM/DD/YYYY - - - Not Started B MM/DD/YYYY - - - On Hold C MM/DD/YYYY MM/DD/YYYY - MM/DD/YYYY In Progress D MM/DD/YYYY MM/DD/YYYY MM/DD/YYYY MM/DD/YYYY Closed And I created a Calendar table, with column Date (extracted from Min(Created Date) and Max(Target Date)) and column YearWeek (WW'YY). And I connect it (column Date) to project table (column Created Date). The issue that I encounter is that, the visual table will show almost every task because of this relationship. So I try to use kinda 'cheatsheet' way by creating a calculated column at the project table: ReferenceDate = VAR TaskStatus = Project[Status] RETURN SWITCH ( TRUE(), TaskStatus IN { "Not Started", "In Progress", "On Hold" }, Today(), TaskStatus = "Closed", WorkItems[Closed Date], BLANK() ) When I select current week, yup, the task that match the condition did appear. Yet, when other week is selected, it will be blank. Is there anyone ever encounter this before or have experience with it when develop gantt chart? How you overcome it? Any guidance will be helpful. Thanks in advance.
Solved
KJChin
1 year ago Place DAX Commands and Tips
434Views
0likes
1Comment
Opening Stock & Closing Stock Calculation
I am trying to calculate Opening Stock and Closing Stock for SKUs on a daily basis, but I keep encountering a circular dependency error when referencing previous day’s closing stock as the next day’s opening stock. Data Details I have a SKU_Date_Mapping table with: SKU (Product ID) Date (Daily records) New Arrival, HL (New stock received) Actual Sales (Sales for the day) Closing Stock (Needs to be calculated) I also have an Opening table with: Ref SKUCode (Maps to SKU) Date (Only first day of each month) Opening Total KHL (Opening stock for the month) Logic Required Opening Stock (Open'HL) If it's the 1st of the month, use the value from the Opening table. Otherwise, use the previous day's Closing Stock. Closing Stock Calculation Closing Stock = Opening Stock + New Arrival - Actual Sales Issue Since Open'HL references Closing Stock, and Closing Stock depends on Open'HL, I am getting a circular dependency error. How can I correctly calculate these values without a circular dependency? I cannot use Power Query as this is a calculated table. Any suggestions would be appreciated!
Solved
nathsonam13
1 year ago Place DAX Commands and Tips
1.1KViews
0likes
4Comments