Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Syndicate_Admin
Administrator
Administrator

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

GuyShare
Units soldAbout the total
OrdersAbout 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:

CLQ_0-1659329671852.png

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.

CLQ_0-1659331382736.png

-->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"
)
(For FiltroShare it's the same.)

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:
CLQ_1-1659330450219.png

(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 2
v-henryk-mstf
Community Support
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

 

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:

NL
1A
2B
3C


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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.