Forum Widgets
Latest Discussions
Data extraction from Pivot
Dear Experts, Greetings! I have a data like below(Worksheet attached too):- Column A(having rntis), B (having slots) and C having the transmission(DL_1_x) Out of this , for each rnti , I need to populate like in the Result, ( I gave 2 examples for rnti's 13648 11808 So, for rnti 13648, transmissions are low in slots 3 & 10, so put "x" in the slots 3 and 10, Similarly , for rnti - 11808, we have less transmissions in slots 0/8/18 so in below Result we see "x" in the corresponding slots. Can you please share some Logic/formula to achieve this? Also, apart from x, if we can also have some data like how much %age loss per rnti per slot for each slots ? Thanks in Advance, Br, Anupam
Visual 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! SergeiBaklanHow can I return one unique match
Hello I am trying to match participant data for work, based on multiple criteria, and I want it to return me an ID number that has not yet been matched. So far I understand that me using the following equation will only return me the first match =INDEX($A$478:$A$9549,MATCH(1,(C2=$C$478:$C$9549)*(D2=$D$478:$D$9549)*(E2=$E$478:$E$9549),0)) Whereas using =UNIQUE(FILTER($A$478:$A$9549,(C2=$C$478:$C$9549)*(D2=$D$478:$D$9549)*(E2=$E$478:$E$9549))) will return me all the unique matches for one ID However, I just want to match up each ID (based on matching three criteria above) with another ID that has not yet been matched. If there is no unique, not yet used, match, would it just be able to return N/A? Thank you for any help you can provide!
How to conditional format any cell in an excel file based on the list of words I have.
I have an excel data that have columns A to AQ and rows 1 to 2022. Means that my data range is A1 to AQ2022. On the other side i have 50 words list, i want an formula to search for these 50 words in A1 to A2022 and highlight any cell that contains the partial text. For example: the 50 words list contains a word "rabigh" then Excel should search for rabigh in A1 to AQ2022 and highlight any cell that contains the partial word "rabigh" or "petro rabigh" or "rabigh power" etc.
IFS does not return correct results
I am using the following formula: =IFS(F7>=$R$1,33,F7>$S$1,35,F7="",0) The formula is returning the first condition and the last condition but not the second condition. FYI, F7 contains a date (and the cell is formatted as a date); R1 also contains a date, S1 contains a different date. This is the condition that is not returning the correct true result. Basically, I want a particular value to be provided if the date in f7 is greater than R1. If F7 is greater than the value in S1, then I want a different value returned. Help please... this has been driving me crazy for a couple of hours!!!
Can't find a way to absolute reference a column name.
So I'm making a table that will give me the value from another table on a different sheet, this sheet constantly gets updated and the column order are always different so I need to use a table name. I was able to make column C work by making A2 absolute Is there a way to make D2 and everything on the right show with the lookup array absolute if I drag them to the right? D2 should give me "SET {SET}" with below: =XLOOKUP($A2,Live[Code],Live[Unit_SNG]) Thank you!
Totalizing value based on 3 column grouping
Hi. I have a table as shown below: I wish to generate a new table, with total length, but the problem is the grouping. The grouping to add length can only occur for rows where the first three values match. That means, Column 1: 16 Column 2: 4C + E Column 3: Cu XLPE/PVC, 0.6/1kV Column 4: 45 (To be added up) can only be grouped with rows where the first three columns match exactly the above values. If the value in any of the first three columns change, that row will become a different group. A coloured example below: The grouping should occur as coloured, adding up the length for each row, and displaying the total length. In the example above, the four purple rows would be merged in one final row, with the length for each one of them being added to generate a total. Is there any way to do this without macros or any extremely complicated method? Best regards,Power Query M Code Not Working When Pasting Into Another Workbook
I have some code that I wrote in Notepad++ basically creating a list and doing a mass find and replace on a column. I pasted it into the Advanced Editor, modified the steps calls, and it worked. The code (adjusted for posting in this forum): #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current Value", type text}}), #"AllReplace" = [#"V1" = "Value 1", #"V2" = "Value 2"], #"Replaced Value" = Table.TransformColumns(#"Changed Type",{{"Current Value",each Record.FieldOrDefault(AllReplace,_,_)}}) When I went to paste the code in a new workbook, the code did not work despite everything being the same. This isn't the first time I've had this issue with pasting in code. Previously, it's just been a bunch of Replace Values steps with no special adjustments to them that I didn't want to recreate in each workbook. What's going on? Any assistance would be amazing here.
Sorting backup: Making a new column from a separate sheet sorted based on original sheet
I've been using Clover for our business for a few years now, but after a recent update I am unable to sort my items by quantity on their website in the category tabs anymore. As an alternative I want to use the exportable excel documents to help us in the meantime. the only problem is I'm not sure how to pull off what I want to do. I also have been unsure how or what to search for to solve this issue to see if anyone else has a solution or not, so apologies if this has been asked before. In the exported document, there is an "Items" tab with all the info for each item. In the "categories" tab, each category is shown with which items are in those categories. Since I sorted our inventory so no items exist in 2 categories, I am wondering if there is a way to auto-populate a new Column in the "items" tab, where a category name can be placed in the new column that matches the corresponding items location in the "categories" tab? Once I can get this figured out then I can easily sort all cells by category, then by quantity!
