Filter overwhelmed? Not showing all data
Hello! I've developed an application for my workplace, logging absences through the year on a backend spreadsheet and presenting the data on a frontend spreadsheet, with a filter available as managers requested the ability to review data based on names, dates, shift times, etc. It is a lot of data, there are 1833 absences logged over the last 2 weeks. The frontend filter continues to work as expected, and there is practically no delay in presenting the data. However, I've noticed that some of the columns are presented as blank. If I open the backend spreadsheet, suddenly the frontend filter shows all columns as populated. I'm not sure how to lessen the load on my filter, as I am assuming there's excess processing afoot. Any ideas worth a try, thank you! Filter code (crunchy, I know. Eek.) The date variables were my only idea to simplify the want to search from, to, and between dates depending on blank cells: " =IFERROR( LET( fromdate,('BACKEND.xlsm]RECORD'!E:E>=G7)*('BACKEND.xlsm]RECORD'!F:F>=G7), todate,('BACKEND.xlsm]RECORD'!E:E<=H7)*('BACKEND.xlsm]RECORD'!F:F<=H7), tweendate,(('BACKEND.xlsm]RECORD'!E:E>=G7)*('BACKEND.xlsm]RECORD'!E:E<=H7))+(('BACKEND.xlsm]RECORD'!F:F>=G7)*('BACKEND.xlsm]RECORD'!F:F<=H7)), blanks,FILTER('BACKEND.xlsm]RECORD'!A:L,('BACKEND.xlsm]RECORD'!B:B<>"")*('BACKEND.xlsm]RECORD'!B:B<>"Co-Ordinator Entering Leave:")*IF(ISBLANK(B3),1,'BACKEND.xlsm]RECORD'!B:B=B3)*IF(ISBLANK(C3),1,'BACKEND.xlsm]RECORD'!C:C=C3)*IF(LEN(E7)>0,'BACKEND.xlsm]RECORD'!D:D=E7,1)*IF(ISBLANK(I3),1,'BACKEND.xlsm]RECORD'!G:G=I3)*IF(ISBLANK(J3),1,'BACKEND.xlsm]RECORD'!J:J=J3)*IF(ISBLANK(K3),1,'BACKEND.xlsm]RECORD'!L:L=K3)*SWITCH(LEN(G7)+LEN(H7),0,1,10,tweendate,5,IF(LEN(G7)=0,todate,fromdate))), IF(blanks=0,"",blanks)),"No Results") " The data / filter results are listed below, of course personal details removed. Columns A, H, I, and K are all presented as blank until the BACKEND spreadsheet is opened (which defeats the purpose of this filter). BACKEND Data RECORDED Co-Ordinator Entering Leave: Employee Name: Date Called: Absence Start Date Absence End Date Shift Time Affected Actioned? Rostered? Reason for Leave Comments Covered by: TRUE John Smith Don Douglas 08/06/2025 09/06/2025 09/06/2025 0900-1730 NO Personal Leave (PL) Sick, recuperating from injury. Note pending. TRUE Jane Smith Deb Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Did not feel well, Victoria moved to cover. Victoria Veck Jane Smith Doug Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Victor Veck FILTER Result ENTERED Co-Ordinator Entering Leave: Employee Name: Date Called: Absence Start Date Absence End Date Shift Time Affected Actioned? Rostered? Reason for Leave Comments Covered by: John Smith Don Douglas 08/06/2025 09/06/2025 09/06/2025 0900-1730 Personal Leave (PL) Jane Smith Deb Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Victoria Veck Jane Smith Doug Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Victor Veck
Organisation Linked Data Type not working
I have a feature table published on Power BI Service that was previously accessible in Excel via the linked data type. However, for the past month, this connection has stopped working, and I'm unable to fetch data from the table. It appears the issue is related to authentication. Even the promoted dataset cannot be found or accessed anymore. This functionality was working fine earlier. I suspect this is due to a change in the authentication or security layer, because when I try to connect to a Power BI dataset in Excel using the "From Organization" option for a PivotTable, I am prompted to authenticate with Azure Active Directory every time. This suggests that persistent authentication is no longer being maintained. I believe this same issue is affecting the linked data type feature in Excel. Could you please clarify Has there been a recent change in the authentication model? Is there a workaround or fix to restore access to the linked data types from Power BI in Excel? Is the linked data type feature being deprecated? II tested on both current channel and beta channel If it's still working on your tenant, please do let me know or if its broken on your side as well , please update so that I can progress the issue accordingly. Thanks
Formula 4 week rotation excluding Fridays and weekends
Hi. I'm creating an employee work calendar. I need to formulas. One i have it but the other one i cannot figure it out. Employee A works from home, called W, one day a week. Week 1 on a monday, week 2 on a tuesday, week 3 on a wednesday, week 4 on a thursday, week 5 on a monday and so on. Fridays not available and saturdays and sunday are weekends. My formula only works if the first of the month is a monday. So i cannot do another month plus i cannot figure it out for employee B when the working from home is a Tuesday for week 1, can someone tell me what i'm doing wrong?
"OVERWRITE EXISTING CELLS WITH NEW DATA..." GET DESELECTED
HELLO , I HAVE OFFICE 365 . FROM 1 MONTH AGO THE BUTTON IN DATA TAB THEN PROPERTIES AND IN IT "OVERWRITE EXISTING CELLS WITH NEW DATA..." GET DESELCTED AUTOMATICALLY AND WHEN WE REFRESH DATA IT GET OVERLAPPED . EARLIER IT WAS WORKING FINE LIKE IN SCREEN SHOT WE HAD SELECTED "OVERWRITE........" TAB BUT WHEN WE PRESS OK AND REOPEN IT IT GET DESELECTED PLEASE HELP ON THIS
Microsoft AutoUpdate error installing Excel 16.97
Hi, I am getting an update error installing Microsoft Excel 16.97 on Mac OS Sequoia. I am currently on 16.96. The install stops near the end of the progress bar for a long time and then I get an update error. I've had similar issues before and resolved by installing from the pkg file but I can't find the 16.97 install file.Better model to improve the performance calculated table/Dax measures
Hi Team, Now, I have an issue with Data model I have developed. Attached is the model i use for my power bi reports that contains 3 dashboards. Invoice quality is one of the dashboards. Below are the requirements to be created for visuals: For the Data[DBName-Point_Id] & Data[Date] columns from Data table, we need to find the status(Dax) as below: *Credit Cancels Invoice -- Number of Credits = Number of Invoices && net cost = 0 *Invoice-Credit Value Mismatch -- ISEVEN(Net invoices) && Net cost <>0 *Multiple Credits -- Number of Credits > Number of Invoices && Net cost <>0 *Multiple Invoices -- Number of invoices >1 && Number of invoices >Number of Credits We need to use this Status dax in visual y-axis of stacked bar chart and use the Count of Dates from Data table as x-axis: Number of Credits,Number of invoices, Net invoices and Net cost can dax or a calculated table columns. Currently, we have created a calculated table using dax logic as below: Invoice Issues = CALCULATETABLE ( SUMMARIZE ( Data, Data[DBName-Point_Id], Data[Date], "Invoice Issue", SUM ( Data[Invoice / Credit] ), "Number of Invoices", CALCULATE ( COUNT ( Data[Invoice / Credit] ), Data[Invoice / Credit] > 0 ), "Number of Credits", CALCULATE ( COUNT ( Data[Invoice / Credit] ), Data[Invoice / Credit] < 0 ), "Net Cost", SUM ( Data[Cost] ) ), Data[Source] = "Invoice" ) Based on the above table, Status calculated column in created in same table: Status = IF ( 'Invoice Issues'[Number of Invoices] > 1 && 'Invoice Issues'[Number of Invoices] > 'Invoice Issues'[Number of Credits], "Multiple Invoices", IF ( 'Invoice Issues'[Number of Credits] > 'Invoice Issues'[Number of Invoices] && 'Invoice Issues'[Net Cost] <> 0, "Multiple Credits", IF ( ISEVEN ( 'Invoice Issues'[Net Invoices] ) && 'Invoice Issues'[Net Cost] <> 0, "Invoice-Credit Value Mismatch", IF ( 'Invoice Issues'[Number of Credits] = 'Invoice Issues'[Number of Invoices] && 'Invoice Issues'[Net Cost] = 0, "Credit Cancels Invoice", BLANK () ) ) ) ) This calculated table is then connected to Points and calendar table DBName-Point_id(points table) --DBName-Point_id(Invoice Issues table) Date (Calendar table) -- Date (Invoice Issues table) This setup causes performance issues especially Status column. Is there any better ways to model this to improve the performance? please let me know if you need further info! PFA file here PR-419 - Data Coverage - Copy (4)_Daxoptimize_copy.pbix Thanks in advance! SergeiBaklanVisual and dax level optimization that causes report slowdown
Hi, I have this visual as attached, that has lot of visual level filters applied it has a dax measure called data completeness as below Data Completeness = var _total = COUNT('Calendar'[Date])*COUNT(Points[DBName-Point_Id]) var _result = [Count of Exisitng Days]/_total return _result It references a dax called Count of Exisitng Days as below Count of Exisitng Days = Var dates = SUMMARIZE(Data, Data[DBName-Point_Id], Data[Date]) Var Ext_dates = COUNTROWS(dates) return Ext_dates Now, the problem I have here is the performance of this visual is causing report to slow down. Please let me know how to optimize the dax and further steps to increase the performance PFA file here PR-419 - Data Coverage - Copy.pbix Thanks in advance! SergeiBaklan
