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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Roel
Frequent Visitor

In a Distinct Union Calculatetable it appears that distinct is interfering with my filters

Hello,

My situation is as follows: I'm trying to create a report that shows the number of distinct articles per different filter and different filter combination we have.  So I have a large table with just the articles and it's surrounded by article property tables, which are connected via inactive connections.

 

My attempt has been focused on unioning a bunch of filtered tables into 1 bigger table and then distinct count that.

 

I have taken it in steps. The below code creates a table in the interface.

 

 

 

UNION(CALCULATETABLE(Article,USERELATIONSHIP(Kleur[Kleur],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Maat[Maat],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Dessin[Dessin],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Materiaal[Materiaal],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Merk[Merk],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Warmteklasse[Warmteklasse],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP('Materiaal tijk'[Materiaal tijk],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP('Materiaal vulling'[Materiaal vulling],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Breedte[Breedte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Lengte[Lengte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Neksteun[Neksteun],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Vulgewicht[Vulgewicht],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Hoekhoogte[Hoekhoogte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Hoogte[Hoogte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Instopstrook[Instopstrook],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Stevigheid[Stevigheid],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Slaaphouding[Slaaphouding],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Eigenschappen[Eigenschappen],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Geslacht[Geslacht],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Woonstijl[Woonstijl],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Draaddichtheid[Draaddichtheid],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Matrasdikte[Matrasdikte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Duurzaamheid[Duurzaamheid],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Dekbedtype[Dekbedtype],'Unieke filters'[Unieke filters])))

 

 

 

This works and gives me 2million lines thanks to all the doubles. So I then wrapped a distinct around it:

 

 

DISTINCT(UNION(CALCULATETABLE(Article,USERELATIONSHIP(Kleur[Kleur],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Maat[Maat],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Dessin[Dessin],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Materiaal[Materiaal],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Merk[Merk],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Warmteklasse[Warmteklasse],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP('Materiaal tijk'[Materiaal tijk],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP('Materiaal vulling'[Materiaal vulling],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Breedte[Breedte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Lengte[Lengte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Neksteun[Neksteun],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Vulgewicht[Vulgewicht],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Hoekhoogte[Hoekhoogte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Hoogte[Hoogte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Instopstrook[Instopstrook],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Stevigheid[Stevigheid],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Slaaphouding[Slaaphouding],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Eigenschappen[Eigenschappen],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Geslacht[Geslacht],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Woonstijl[Woonstijl],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Draaddichtheid[Draaddichtheid],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Matrasdikte[Matrasdikte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Duurzaamheid[Duurzaamheid],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Dekbedtype[Dekbedtype],'Unieke filters'[Unieke filters]))))

 

 

This gives me a table of 64k distinct rows.
But because it's loaded in a table it's not reactive to filters so I put that into a measure
 

Below what I made:

#Aanbod alle filters calctable =

 

 

COUNTX(DISTINCT(UNION(CALCULATETABLE(Article,USERELATIONSHIP(Kleur[Kleur],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Maat[Maat],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Dessin[Dessin],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Materiaal[Materiaal],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Merk[Merk],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Warmteklasse[Warmteklasse],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP('Materiaal tijk'[Materiaal tijk],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP('Materiaal vulling'[Materiaal vulling],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Breedte[Breedte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Lengte[Lengte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Neksteun[Neksteun],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Vulgewicht[Vulgewicht],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Hoekhoogte[Hoekhoogte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Hoogte[Hoogte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Instopstrook[Instopstrook],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Stevigheid[Stevigheid],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Slaaphouding[Slaaphouding],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Eigenschappen[Eigenschappen],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Geslacht[Geslacht],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Woonstijl[Woonstijl],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Draaddichtheid[Draaddichtheid],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Matrasdikte[Matrasdikte],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Duurzaamheid[Duurzaamheid],'Unieke filters'[Unieke filters])),
CALCULATETABLE(Article,USERELATIONSHIP(Dekbedtype[Dekbedtype],'Unieke filters'[Unieke filters])))),[articlenumber]))

 

 

 

<div>This gives the 64K results, but oddly doesn't change with when I use any of the connected filters.<div>However, when I remove the DISTINCT I get the 2 million lines but those 2 millions lines do change when I use the filters. So it seems like the distinct is somehow interfering with my filters. As such I tried several variants, including moving the table calculation into a variable and keeping the distinct out. That didn't do anything different though.

Changing the DISTINCT into a GROUPBY function unfortunately also doesn't work. The moment I remove duplicate rows the measure stops responding to my filters.

Does anyone know why Distinct is interfering with my filters or does anyone have a solution to my problem? Any help would be appreciated.

 

edit: I edited this post just now due to a whole bunch of errors my browser was giving me. Ultimately it went through but was littered with HTML tags which I just removed.

1 ACCEPTED SOLUTION
Roel
Frequent Visitor

Okay,  I found my problem. I'm an idiot.

 

The 64k is the total number of products and because I have an and/and/and function going I always end up with a complete product list.

 

For completeness sake. What happens is:

list with filter is combined with another product list with filters and then distinct does it's magic. However if one of many lists isn't filtered the entire productlist is added. Which means filtering never does anything unless you use all the filters. Closing this.

 

Thank you for the initial replies.

View solution in original post

5 REPLIES 5
Roel
Frequent Visitor

Okay,  I found my problem. I'm an idiot.

 

The 64k is the total number of products and because I have an and/and/and function going I always end up with a complete product list.

 

For completeness sake. What happens is:

list with filter is combined with another product list with filters and then distinct does it's magic. However if one of many lists isn't filtered the entire productlist is added. Which means filtering never does anything unless you use all the filters. Closing this.

 

Thank you for the initial replies.

amitchandak
Super User
Super User

@Roel , Can you explain your use case; I am not able to understand the need of use relationship in case of, create table.

 

How many time you join articles with others , it will give same set of articles. Unrelationship is to change it at run time for a measure.

 

refer: https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

 

I think you need create measures as per need.

But need to know more details.

I will try to explain in more detail.

 

We have an ecommerce website. When someone uses a filter on the website the site gives you the articles that have that property.

Then let's say the customer selects another filter, but doesn't turn off the previous filter. What happens now on the website is that the articles that have any of the two filter properties is shown on the website.

 

What we're trying to do is use this to check if there are often used filter combinations which don't provide a lot of products in return.

 

Now normal behaviour within PowerBI when you use a filter is that the articles are dropped and you are only shown the articles that have that property. So I need to make sure everything is returned and deduplicated.

 

At base level I basically need 2 tables. The base article table and the property table.

The property data is saved in one big SQL table. The easiest solution would obviously be to just use that huge table. But because it contains everything that table is unwieldy, near impossible to use in the interface.

So I split that table into several smaller filter tables that are connected to the base article table. But doing it like that causes the table to be an and filter.

 

So I figured I would try it by creating a bunch of separate tables and union them together and deduplicate them.

 

I hope my explanation is clearer this way. Obviously, if you or anyone else has a better method of achieving my goal then I'm open for suggestions.

 

 

I'd love to provide an image, unfortunately my Monday morning of off to a terrific start and my PBI is corrupted and unusable. 😞 I need to completely rebuild it as I don't have any backups yet.

What @amitchandak said and can you also provide a screen snip of your data model without the big giant table?

I'd love to provide an image, unfortunately my Monday morning of off to a terrific start and my PBI is corrupted and unusable.

Roel_0-1610354465446.png

 

I need to completely rebuild it as I don't have any backups yet. But maybe my expanded explanation will help understand what I was trying to say while I rebuild.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors