Matrix visual not displaying the expected result

[Maria7]

Hello everyone,

I need some help with a problem I am encountering when creating a visual matrix table.

I have 3 measures that are calculated in different tables, a Data_Table that is connected to one of these tables and a table from where i take the filters that I want to apply on the matrix visual. Also, the tables are connected using the many-to-many relationship with both filters crossed and the Data_Table is a one-to-many relationship with one of the 3 tables containing the measures.

The problem is that when I try to filter the visual matrix, which contains all 3 measures ( per month column from Data_Table), with filters from another table, the values are incorrect and even if I change the relationship, I get the same result. But when I want to filter the measures with columns that are in the same table with them and the Data_Table is connected to this specific table, I get the correct results.

The logic of the formulas is mostly the same: Measure1 = CALCULATE(
SUMX(
FILTER(TableName, TableName[Type] = "abc"),
TableName[ValueColumn]
)).

Thank you in advance for any help!

[lucadelicio]

Another idea is in Power Query to create a single table that is the append of table + table1 + table2 with one more column that rapresent the DERIVATION TABLE (table, table1, table2).
Pay attention that in the append you must have the same name for the same columns.

In this way the time table insist only on one table that is the FACT table.
You have only one measure.
And in the matrix you put in the row the DERIVATION TABLE,
in the columns the period
and the measure as a value.
I think like this is more correct.

[Maria7 · Accepted solution]

I tried this before but I couldn't append those 3 tables because I didn't have the same columns name. 

But I found a workaround. I created a calculated column in each table where I concatanate the Subcategory and Category and I linked all the tables through this columns and then I was able to link the Data_Table with every table. After this I was able to see in the matrix the right values on the right column data for each measure.

Your idea of linking those 3 tables via calculated columns was useful, but the formulas were not changed because I got the correct result after I worked on the relationships.

Thank you @lucadelicio for your ideas!

[lucadelicio]

Happy to be helpful.

[Maria7]

Hi,

I can't upload a pbix file, I don't have this option unfortunately 😞

Instead I can attach some screenshots of a test file that I created, because I can't share the data I'm working on due to data privacy, if that helps 🙂

Here are all the tables and the relationships between them:

 The content of each table is shown below.

Filter table:

 

Table:

 

and the measure created for this table is :

Measure_Table = CALCULATE(

    SUMX('Table', 'Table'[Quantity]))

Table1:

the measure created in this table is :

Measure_Table1 = CALCULATE(

    SUMX(

        FILTER('Table 1', 'Table 1'[Type] = "abc"),

        'Table 1'[Quantity]))

Table2:

the measure created in this table is :

Measure_Table2 = CALCULATE(

    SUMX(

        FILTER('Table 2', 'Table 2'[Type_column] = "sea"),

        'Table 2'[Quantity]))

- Data_Table is a new table created in Power Query containing data from 2020 to 2120.

Here is what I get when I create the matrix visual and filter him with data from the filter table. The months are from the Data_Table.

And I should have, for example for the Measure_Table the following month and value as it can be seen below:

or for the Measure_Table1 this result

Thank you!

[lucadelicio]

Try to simplify the relationship one to many.
Add a calculated column as a KEY on all table like a CONCAT Category+Subcategory.
So you have a relation 1 to *.
Then i suppose in the matrix you put the time table but this time table it insist only on the table2.
Create a calculated column data on the table and table 1.
So the measure on the table 1 and table it could be revisited like this:
var _min = MIN(Data_Table[Data])
var _max = MAX(Data_Table[Data])
RETURN
CALCULATE(

    SUMX(

        FILTER('Table 1', 'Table 1'[Type] = "abc"),

        'Table 1'[Quantity]
        )
,ALL(TABLE1[Data])
,TABLE1[Data] >= _min and TABLE1[Data]<=_max
)
Now the table 1 is working in the same period of the selected one in the table 2 on the matrix.
I think it could be this the problem.
Let me know if this suggestion could be a solution.
Ciao

[lucadelicio]

Can you upload the pbix file?
Thanks