Matrix invalid relationship

[Anonymous]

Hello,

I have a little problem with matrix. I m putting values from 2 tables. Second table doesnt have as much categories as the first one and when i m expanding categories there is a problem with showing max values which shows invalid relationship. Its logical

1st tables have 3 categories when the 2nd one has only 1

A little tip how could i hide the incorrect values and leave the specific categories blank after expanding

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

NFF qty = 
  IF( 
    NOT(ISINSCOPE('pl_master_data naprawy'[uszkodzony])),
    SUM('pl_master_data nff'[qty]))

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Well the function is short with one column but the logic works great. Problem solved

[Anonymous]

All the left is to put at the end of matrix sum of NFF qty.

@amitchandak any thoughts?

NFF qty = 
  IF( 
    ISFILTERED( DimReworkPN[pn]) && NOT(ISFILTERED('pl_master_data naprawy'[uszkodzony])) && NOT(ISFILTERED('pl_master_data translate'[Defects])),
    SUM('pl_master_data nff'[qty]))

[v-jianboli-msft]

Hi @Anonymous ,

Based on your description, I have create a simple example:

Apply the measure:

Measure = IF(ISINSCOPE('Table'[Subcategory]),BLANK(),CALCULATE(SUM('Table (2)'[Qty]),FILTER(ALL('Table (2)'),[Category]=MAX('Table'[Category]))))

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It looks promising but the behaviour doesnt apply to Category B,C.

Each Category need to be preserved without subcategories values in 2nd column

Category A 22

Category B XX

Cat C XX

[Anonymous]

Sorry but i cant share the file.

Qty columns depend from specific tables.

Table 1

Table 2

Only 320 category 22 value is valid for second column, expanding matrix with number that doesnt exists. Classic case of invalid relationship, max of the upper category ( should be blank). Table 1 and 2 share DimTable with 320 category so the sum will be valid for them in that case.

[Anonymous]

22 value is valid only 320 category, for the rest of the them is showing max

[v-jianboli-msft]

Hi @Anonymous ,

Sorry, I'm still not quite sure what you mean. What is the difference between Qty and qty? Which table are they calculated through? You mentioned that "22 value is valid only 320 category, for the rest of them is showing max" does that mean 22 is the sum of 320 category types? Should the rest of the values be showing max? Please correct me if I am wrong, and if you can, please provide a simple sample without sensitive data and expected results to help me understand your question better.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft]

Hi @Anonymous ,

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.