Hello all. I am trying to optimize a little bit my report.
I have two main objects which are related. The main one it is called Ticket, and it has an ID and Status
The second one are tasks, with contains its own ID and the ParentID which is the ticket ID where the task is linked to
I have filtered the Tickets table to keep only the status not closed.
Nevertheless regarding the tasks I need to remove the rows based on the tickets remaining on the Tickets table so I can filter it properly.
I have thought about some ways of doing it, but it looks like the syntax is not correct. It looks like the columns created on the Query Editor are quite limited.
Any suggestion on how to do it so I can prevent to get 500.000 rows each refresh where 95% are not relevant for the dashboard I am working on?
Thanks in advance
Solved! Go to Solution.
@Chavas , as far as I know, the Power Query way to merge the table using an inner join, if need create a column using header and filter blank
So merge the header(after rows from header deleted) with details using inner join and expand and take status from the header. Ideally, it should reduce the rows. If not filter the rows here too based on the status.
Thansk @amitchandak I have tried with the Merge option, but it does not work because I want to prevent getting ALL the data each refresh. So this needs to process everything but I want to remove the rows based on a condition of: If Task.Value=Incident.Value, keep row, remove row.
@Chavas - Not entirely clear on this, sample data and expected output would help. Maybe something like EXCEPT?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi @Greg_Deckler . Thanks for your answer.
I have been looking for the solution but I should be doing something wrong because the RELATED function is not being accepted by the New Column editor.
I have a table such as:
With 1M Records. I have filtered to filter all with status C. So it just speed up my refresh process because it only process 500 rows.
I have also the Task table:
This one haves let's say 5M rows, and I need to filter it and only keep the data of the Tickets which are relevant (The ones that where not filtered before) In this case, Task with ID 3 should be removed. so instad of processing 5M entries, I will just process 5K.
I have tried to create a new column, but each formula I set to try to get the data it does not work, the IDE does not let me set a VLOOKUP or Related formula even an IF.
Simpliet way is pseudo code will be creating a new column:
If (Exists in Ticket Table (ID=Task.TicketID), 1,0)
DELETE ROW IF Exists=0
@Chavas - Oh, you are in Power Query, sorry, those commands I mentioned are DAX. Well, in Power Query, did you try the Left-Anti or Right-Anti joins? Let me look at this with a fresh set of eyes.
Thank you both ( @Greg_Deckler and @amitchandak ) for your assistance, what I am trying, it is something like this
SELECT TaskID, DATABASE.dbo.Ticket.TicketID, DATABASE.dbo.Ticket.Status FROM DATABASE.dbo.Task
inner join Database.dbo.Ticket on DATABASE.dbo.Task.ParentID=Ticket.ID
WHERE Ticket.Status != 'Closed'
So I have already filtered the Ticket table but I wont to prevent loading tons of rows which I do not need and I am not going to use it. So I do not need to have on a new table all the info of the Tickets, you know? I would like to keep it separated and then I will manage it through the relations.
I might posted it in the wrong forum :S And sorry for the confusion
@Chavas I strongly recommend you do not use the Advanced View with the SQL statement. It prevents further folding, it prevents incremental refresh, can can cause permissions to need to be elevated for a gateway connection.
If you must use as SQL statement, create a view on the server and connect to that.
That said, you can do this 100% in Power Query against a SQL server and it will fold, as long as you do not use that SQL statement box.
What this does is convert this to an IN statement in SQL, then does the opposite, so Not In basically. I don't have your tables in SQL Server but I tested this with the WorldWideImportersDW sample db from MS. This is the code it generated in the "View Native Query box. The server will process that and return the results.
Any questions ping back.
That is a solution!
But the problem is that I have to hardcode the status in the query. In a future, If I want to change it, I will need to redo the query or minding that I have hardcoded somewhere.
Is there any other fancy/more elegant option?
(I am quite new to PowerBI, so your solution might be the way)
Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.
Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!
Watch Session 22 Ted's Dev Camp along with past sessions!
Click here to read more about the May 2022 updates!