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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Maria7
Frequent Visitor

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 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!

1 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!

View solution in original post

6 REPLIES 6
lucadelicio
Super User
Super User

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.

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

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!

Happy to be helpful.

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/
Maria7
Frequent Visitor

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:

Maria7_0-1688054934140.png

 The content of each table is shown below.

Filter table:

Maria7_1-1688055090711.png 

Table:

Maria7_2-1688055166400.png 

and the measure created for this table is :

Measure_Table = CALCULATE(
    SUMX('Table', 'Table'[Quantity]))

Table1:

Maria7_3-1688055207604.png

the measure created in this table is :

Measure_Table1 = CALCULATE(
    SUMX(
        FILTER('Table 1', 'Table 1'[Type] = "abc"),
        'Table 1'[Quantity]))

Table2:

Maria7_5-1688055309322.png

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.

Maria7_6-1688055918192.png

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

Maria7_7-1688056019815.png

or for the Measure_Table1 this result

Maria7_8-1688056059661.png

Thank you!

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
Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/
lucadelicio
Super User
Super User

Can you upload the pbix file?
Thanks

Luca D'Elicio

https://www.linkedin.com/in/luca-d-elicio-74a481158/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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