Forum Discussion
Efficiency problem
Hi, I have an issue with the efficiency of a solution to a problem. Does anyone have improvement suggestions to improve speed?
I have the basic settings in Power BI desktop, but the solution (see below) to the problem (see below) is far too slow. My data usually ranges from 50 000 - 1 000 000 rows, so looping "find" is probably too slow. Any suggestions for a more efficient solution?
Problem
For people familiar with accounting: I would like to see where do entries from one account go to. As in "other side of a journal entry".
Basically the data is this as in the table below:
| Account | Identifier | Sum |
| 1000 | 565 | 100€ |
| 1100 | 565 | 20€ |
| 1200 | 565 | -120€ |
| 1200 | 572 | -101€ |
| 1300 | 572 | 50€ |
| 1400 | 572 | 50€ |
| 1500 | 572 | 1€ |
| 1600 | 577 | -5000€ |
| 1600 | 577 | 5000€ |
Of course, there will be hundreds of thousands of lines. But the rule is:
1. All Identifiers (565 for example) equal out to zero in the Sum-field.
Goal:
I would like to create a dashboard, where i can filter account 1200 and it would present to me the following:
| Account | |
| 1000 | 100€ |
| 1100 | 20€ |
| 1300 | 50€ |
| 1400 | 50€ |
| 1500 | 1€ |
How do i start to create this? So the function would be like this:
Filter all Identifiers that are in account 1200 (565 & 572 in this case).
If account <> 1200 then SUM the values of these identifiers and show which account they are assigned to
Solution:
Anonymous ,
Create an additional table using dax below:
Account = DISTINCT('Table'[Account])Then create slicer based on the new 'Account' table and create measure using dax below:
Result = VAR SelectedAccount = SELECTEDVALUE(Account[Account]) RETURN CALCULATE(SUM('Table'[Sum]), FILTER(ALL('Account'), 'Account'[Account] <> SelectedAccount))You can also refer to pbix attached.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi,
Thank you, elegant and efficient! I need to further look into this, as I really don't understand why the relationships are required in the background and if I would like to add a second column to the filter and the table (for example account name) it does break down.
But these are issues that should be solved by me now that I have the core.
Thank you!
Anonymous ,
Create an additional table using dax below:
Account = DISTINCT('Table'[Account])Then create slicer based on the new 'Account' table and create measure using dax below:
Result = VAR SelectedAccount = SELECTEDVALUE(Account[Account]) RETURN CALCULATE(SUM('Table'[Sum]), FILTER(ALL('Account'), 'Account'[Account] <> SelectedAccount))You can also refer to pbix attached.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.