filtering/connection problem

[andris_]

Hey everyone!

I'm facing a problem because of the stupid data connections, I guess. I have datas from 3 different tables, none of them have unique values, so I have a table with unique values which one is the connection between them. (connected with the machines' - we are talking about printers - Service Item Number unique values)

I have a Cost table, which contains the costs about every machine. Of course, one machine can have more than one (type of) cost.

The other table is the Page Volume table, which contains the amount of printed pages per machine per month.

I've made some measures, which calculates the cost per page for the machines. They are working well, based on the results. BUT. And here comes the point:

I'd like to filter those machines, which ones' page volume is less, than 500. I created a column, which calculates the summarized page volume for every machines (page volume for every unique Service Item Number), but if I filter the page or the visual with the created column, it does not work well with the Cost table (only for the Page Volume table), so it ruins the cost per page values.

What I'd like to do, is to take out the Service Item Numbers which have less (or more) page volume, than 500. And filter them, using the common connection table, which is connected to both of the tables.

I hope it's clear, I know the context is complicated, but I think the problem itself is not, but if something is not plain, don't hesitate to ask me about it.

Thanks in advance,

Andris

[vanessafvg]

@andris_  best way to get an answer quickly is to post a screen shot of

1. your relationship view

2. an example of when it works and then when it doesn't

[andris_]

@vanessafvg thanks for your reply!

So here is the relationship view, I enhanced the relevant tables with red (yeah I know, it's quite lame). The Cost table is about the costs, the Data table is the Page Volume table, and the Munka1(2) table is the connection between them with the uniqe Service Item Number values.

In this table, the first column is the Service Item Number column from the Page Volume table, the second one named "Column" is the created column which aggregates the page volumes per machines, the third column is the Service Item Number from the Munka1(2) table, which has the unique values (I put it there in order to check, that every value has the Service Item Number from the connection table). It is filtered to show only the machines which have more, than 500 page volume (aggregated).

So, I'd like to filter by those Service Item Numbers, which have a "Column" value bigger than 500.

Finally, this is a photo about the difference with the measures I mentioned before in the post. You can see the difference, the first two values, which ones' denominators get the values from the Cost table, are way too low using the "Column" as a visaul level filter.

Best regards,

Andris

[v-ljerr-msft]

Hi @andris_,

Could you try modifying the cross filter direction for the relationships among the three tables like below to see if it works. 

Munka1(2) table and Cost table: Both

Munka1(2) table and Data table: Both

Data table and Date table: Single

Regards

[andris_ · Accepted solution]

Hi @v-ljerr-msft,

First of all, thanks for your reply! 🙂

I tried it, but after I modified the connection between the Data and Date table to single direction, and then modified the connection Munka1(2) table and Cost table to both direction, and tried to modify the connection between Munka1(2) table and Data table, it gave me this error message:

By the way, in the meanwhile I found a solution which seems to be working. As I mentioned in my original post, I created a column with DAX in the Data table, which is the following: 

Column =

CALCULATE((SUM(data[A4])*2)+SUM(data[A4C])*2)+SUM(data[A3])+SUM(data[A3C])

;ALLEXCEPT(data;data[Service Item No]))

The first part is about calculating all the page volumes in A4 paper size. So, this function gave me for every machine type the page volumes, but if I wanted to filer with it, it did not work with the datas from the Cost table. So, basically, I created the same column in the Munka1(2) table (which is the connection between Data and Cost table). Here it is:

Page Volume on Different Machines =

CALCULATE((SUM(data[A4])*2)+(SUM(data[A4C])*2)+SUM(data[A3])+SUM(data[A3C])

;ALLEXCEPT('Munka1 (2)';'Munka1 (2)'[Service Item No.]))

With this function, I have the same values, and it filters every visualisation and data from the Cost table.

Best regards,

Andris

[v-ljerr-msft]

Hi @andris_,

Nice solution!

Could you accept your reply above as solution to close this thread, which will also help others who may also have the similar issue easily find the answer? 

Regards

[andris_]

Yeah, @v-ljerr-msft, done. 🙂

Thanks for your help and helpful manner!

Best regards,

Andris