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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
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!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.