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
Getting updated data from a Microsoft Form's SharePoint Excel Repository using VBA.
I want to grab data from an Excel file in SharePoint that is synced to a Microsoft Form and put it into a different file using a VBA macro, but I don't get the updated data. I have to manually access the Excel file that is a repository of the Microsoft Form Responses and let it sync to update. Then I can run the VBA. But I want to cut that manual step out. How can I do this?? I've tried many things (Power Automate, VBA updates, etc.), but no luck so far. I saw that I may have to use forms inserted through Excel online to get a file that refreshes immediately after each Microsoft Form submission, but that would mean I have to rewrite the Form and the VBA I currently have.Excel Sumifs
I need a formula to return how many shares of each stock I still own; Subtract # Sold from # Bought for each ticker and return the number remaining of each stock sorted by ticker. No return if the result is “0”. The result should be: AFRM 15 BBCP 50 FRME 10 GILL 20 PLTR 20 STAF 150 TSLA 10 Ticker Trans Units AFRM Bought 15 TSLA Bought 10 UPS Bought 10 BBCP Bought 100 GIII Bought 30 STAF Bought 2 BOXL Bought 500 STAF Bought 298 PLTR Bought 20 FRME Bought 19 FRME Bought 1 GIII Sold 10 FRME Sold 10 STAF Sold 6 STAF Sold 142 STAF Sold 2 BBCP Sold 50 BOXL Sold 500Charting doubt_1
Dear Experts, I have an issue like below, so in the Worksheet "SssgSwitchEvaluation_Study_curr", I want to make a graph like below with the Variables, Time, CurrentState, slot and Time(ms) as below But problem is that at 504 like below we know that the current state is changed to sparse(from dense), but how to shown that in a Graph? when it's non-numeric(I must say I did that earlier and some times it worked, not sure what's changed now)? also the slot I tried as Secondary axis, but not sure what mistake I am doing, it shows a straight line..(slot can range from 0~19), Attaching the worksheet. :- Thanks in Advance, Br, Anupam
BROKEN FILTER FORMULA AFTER UPDATE QUERY
Can someone please help, my filter formulas seems got broken after i update my query. I make table in several sheets cointaining filter function, the purpose is to find the MAX and MIN of data percentages from certain range of dates with different criteria this is the formula =IF(COUNTIFS(財務比率[CODE];[@CODE];財務比率[Date];"=0");"NO DATA";MAX(FILTER(財務比率[Assets, b.IDR T-1 %];(財務比率[CODE]=[@CODE])*(財務比率[Date]>=歴史[@[MONTH L]])*(財務比率[Date]<=歴史[@[MONTH BB]])))) 財務比率 sheets is the source data 歴史 sheets is the query table containing date criteria everything is fine until i updated query 歴史, adding new row "CENT 4."(Column NEW CODE) and now most of my MAX and MIN formula return error #CALC! with explanation "Empty Array. I`ve checked file sources for 歴史, check power query got no error, and tried rewrite the filter formula, still no luck i also notice my file size got smaller from 51MB to now 46MB, suggesting there is data loss. This had been several days now, please help 🙏 Trend Up History Broken Filter
Utilizing Excel's turing capabilities to create Conway's 'Game of Life'
The Background It's been said with Lambda (and LET and a wealth of functions in 365) Excel has become 'turing-complete'. To quote the article linked below: "You can now, in principle, write any computation in the Excel formula language." https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/ The Challenge I thought it would be fun to create Conway's 'Game of Life' in Excel 365 to see how far I could push things. Conway's Game of Life - Wikipedia The rules are simple: A 'cell' has up to 8 adjacent cells (less if the cell is on the edge of the board). A 'neighbor' is a cell with a 1 while a 'dead' cell is empty. An 18x18 board Multiple iterations Bigger boards! more (it's relaxing to create new shapes and designs) The Approach My first thought was to use MAKEARRAY because I could use 'r' and 'c' coordinates and there would be no stacking. I devised a recursive function that worked for 1 iteration but failed on subsequent iterations because the use of TAKE/DROP was slowly shrinking the board! The revised approach is essentialy a recursive MAP that uses 3 arrays: the input matrix, the 'r' array (row numbers) and the 'c' array (column numbers). It's my way of using r/c without using MAKEARRAY. For Discussion I welcome any improvements to the existing function and any different approaches someone may have to creating Conway's Game of Life. Conway Lambda follows: Conway =LAMBDA(matrix, iterations, IF( iterations = 0, matrix, Conway( LET( height, ROWS(matrix), width, COLUMNS(matrix), r_arr, SEQUENCE(height) * SEQUENCE(, width, 1, 0), c_arr, SEQUENCE(height, , 1, 0) * SEQUENCE(, width), CheckNeighbors, LAMBDA(lattice, r, c, LET( RCx, LAMBDA(row, col, IFERROR(CHOOSECOLS(CHOOSEROWS(matrix, row), col), 0) ), N, RCx(r - 1, c), NE, RCx(r - 1, c + 1), E, RCx(r, c + 1), SE, RCx(r + 1, c + 1), S, RCx(r + 1, c), SW, RCx(r + 1, c - 1), W, RCx(r, c - 1), NW, RCx(r - 1, c - 1), compass, VSTACK(N, NE, E, SE, S, SW, W, NW), neighbors, SUM(compass), IF( AND(lattice = 0, neighbors = 3), 1, IF( AND(lattice = 1, OR(neighbors = 2, neighbors = 3)), 1, 0 ) ) ) ), MAP(matrix, r_arr, c_arr, CheckNeighbors) ), iterations - 1 ) ) )
How to create a shared company calendar for time off and holidays?
I'M LOST I have spent DAYS trying to figure out some way to provide a shared company calendar with Holidays, PTO Schedules, On-Call Schedules, etc. We just want one place to see what's going on this week, who's available, and do we have a day off this week. BASIC information. After spending more than 3 days on this, I feel more lost than I did when I started. I could create a SharePoint Calendar. This calendar doesn't sync with Outlook for Mac or Apple Calendars and, most importantly, reoccurring events don't show up on a SharePoint Events web part. I could manually create reoccurring events but I rather not. Regardless, the functionality is incredibly limited. I could create a new M365 user. This would let me see these calendar in Apple Calendars and give myself or another admin access to update events. But this calendar can't be integrated in SharePoint or Teams so it's basically invisible. I could create a Channel Calendar in Teams but that's certainly not appropriate. I could create a new Group with a Calendar. Although, we already have a org-Wide Team and it doesn't actually do anything - can't email [email protected] and it doesn't come with an Outlook calendar. I don't think I should have to add someone to a group for them to see a calendar I want public to the entire company. This might be the best compromise but that's besides the point. The POINT is, WHY in the world isn't this a built-in feature that's turned on the moment a company creates a M365 tenant? WHY do I need to spend days figuring out that all the archaic tutorials from 2017 don't even work in 2021? WHY can't MICROSOFT make calendars easy? I mean, I'd settle for difficult - it's better than impossible. And, not for nothing, I look like a complete idiot (I very well may be!) when my boss asks me to do something that should take 2 minutes and I have absolutely nothing to show 3 days later. Q: What is your business doing to let you know you have Memorial day off? How are you able to see if someone has the week off for vacation? Are you using a M365 product or something else?
Populate numbers/letters in long list...???
Hello - I often need to populate long lists in excel and would like to know if there is any way to this in cells using numbers/characters/letters? For example... I have a cell filled with "U02_01P" and I would like to fill down remaining cells changing the numbers before "P" to the next sequential number ("U02_02P, U02_03P....). Is there an easy way to do this?? I'm not an excel expert - so an y help would be appreciated!
Excel Powerpivot based spreadsheet freezes all office aps
I have a powerpivot based excel sheet that I have had for years without issues. Its a bit slow as it has to run a function across multiple stock webpages to extract stock price data. In the past when it was refreshing I could still use my Outlook, but not outlook is frozen until the refresh finishes. In fact while it is running a background query you cannot get excel to maximise on the screen it just hides in the taskbar. I have done a full reinstall of Office and still the same issue. Its extremely irritating, but I dont know what could have changed. Weirdly if I actively click refresh all in excel I can still use Outlook, so only when the automated refresh set for every 10 minutes activates is there an issue. Any thoughts appreciated
