Forum Discussion
Delete relationship between columns to correctly apply two filters to an array
Good people, I'm just starting to play with Power Bi with a database that I downloaded and I'm currently stuck in something, I tell you and the last thing I leave the formulas I used:
I have the following table, called FilterSalesDetails
| Guy | Share |
| Units sold | About the total |
| Orders | About the category |
| GR$ | About the brand |
| Hl |
|
My idea is to create an array where there are two filters: one for "Type" and one for "Share", here I show you my matrix:
If I select an option in the "Type" filter, I should change only the columas "FilterType" and "FilterVariacionMes1", the "FilterShare" column should only be changed when I press something in the "Share" filter. What I did works half-heartedly:
If I select an option in "Share", it effectively gives me the result I expect in the "FilterShare" column and if I select a value in the "Type" filter it correctly shows me the values for their two respective columns, but here the problems begin:
-First: when I select something in filter "Type", it also shows me a value in "FilterShare" and this has to do, I suppose, """with the relationship between the columns of the tables"", I explain: if I select "Units sold", in FiltroShare I see the value corresponding to "About the total" (since, seeing the table above, it is "next to"), the same if I select Orders-->About the category, etc.
-->Example: here in FilterShare should appear "Select Filter", instead the value that shows "About the total" of the Share filter appears.
-Second: as there is clearly a problem in that """relationship"", if I select in the first filter, "Units Sold" and in the second filter one other than "About Total", I see "Select Filter" (which has to do with the formula I use, I leave them below). For example, if I select "About the total" in the Share filter, I will appear in the first two columns "select filter" and in the third column, the values shown in the image above, and if I leave both selected, it is also as it is that image above, but if I select for example "About the category", me appears in all columns "Select filter"
So, here are the formulas:
SelectTipo = SELECTEDVALUE('FiltrosSalesDetails'[Tipo])
SelectShare = SELECTEDVALUE('FiltrosSalesDetails'[Share])FiltroTipo = SWITCH(TRUE(),
[SelectTipo]="Units sold", calculation,
[SelectTipo]="Orders", calculation,
[SelectTipo]="GR", calculation,
[SelectTipo]="HL",calculation,
"Select Filter"
)
So: first with SelectTipo/Share I make me select the values of the respective column of the table, then, with the switch, I make me calculate something (which returns it well).
As I said above, there is clearly a problem in the relationship between the rows, which I cannot remove. I tried to edit the interactions, staying like this:
(same at the time, Tipo I blocked it with respect to Share) but it didn't work.
I know that this could be easily solved by creating another table with those 3 rows (or 4, depending on which column you choose), but let's say that I do not want to because I am stubborn or does not walk the Little Power Bi 😅 button, I would like to know if there is any solution since I did not think of anything.
Of course, thank you very much
2 Replies
- v-henryk-mstf
Community Support
Hi Syndicate_Admin ,
According to your description, is it possible to provide the relevant test files. It is convenient for me to answer for you.
Looking forward to your reply.
Best Regards,
Henry- Syndicate_Admin
Administrator
Hello, good, sorry for the delay. Being a dataset that I downloaded random from the Internet it is not possible since I changed it a lot, also creating a table like the one I did above in the original post (or any table with two rows) the same thing happens, that is to say that if I have:
N L 1 A 2 B 3 C
the rows would be related, i.e. 1 to A and vice versa.
As I mentioned, if I created two filters and selected, example "B", the results would automatically be skewed by "2".
I solved it by creating two tables, one for each column, since I found no way to remove the relationship, nor do I know if there is such a possibility. The solution is simple obviously, but hey, if there is a way for this not to happen and avoid creating a table just for these cases would be great! Best regards