Forum Discussion

Maria7's avatar
Maria7
Frequent Visitor
3 years ago
Solved

Matrix visual not displaying the expected result

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 t...
Need Help
  • Maria7's avatar
    Maria7
    3 years ago

    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!

Maria7's avatar
Maria7
Frequent Visitor
3 years ago

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's avatar
lucadelicio
Impactful Individual
3 years ago

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