topic Re: Row value not showing correct in Matrix in DAX Commands and Tips

Row value not showing correct in Matrix

Anonymous — Mon, 02 May 2022 07:50:30 GMT

Dear Community , i have a problem with a matrix i can't seem to get correct in PBI.

My matrix has 3 rows

- customer

- agreement

- project

It is possible for a project have the same project keys but different agreements. when i try to filter it out in the matrix, the matrix is not able to filter out correctly, because of the identical project key. in the example below there is two different agreements, where the agreements has the same project (KOGS). this results in the agreed estimate to show up as 213 for both agreements and projects.

But this is wrong.

in my database if i do the same filtering the results comes up like this - which is the correct result:

the calculation used for agreed estimate is:

Afreed estimate = CALCULATE(IF(HASONEVALUE(JIRA_Issues[Agreement_Name]) = FALSE(), SUMX(JIRA_Issues, [IssueAgreedEstimates]), blank()), Filter(JIRA_Issues, JIRA_Issues[Agreement_Name] <> blank()))

hope somebody can help me as i've been stuck here trying to figure out a solution for quite some time.

cheers.

Re: Row value not showing correct in Matrix

lbendlin — Thu, 05 May 2022 01:35:49 GMT

seeing the same numbers in a column usually indicates that there is a kink in your data model. Looks like you have multiple tables involved?

Re: Row value not showing correct in Matrix

Icey — Mon, 09 May 2022 07:15:57 GMT

Hi Anonymous ,

Based on the information you provided, I don't think HASONEVALUE is required in your scenario. Have you tried to just create a measure like below?

Afreed estimate = SUMX ( JIRA_Issues, [IssueAgreedEstimates] )

In addition, if this doesn't work, what's your data structure? Could you give me some sample?

Best Regards,

Icey

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

Re: Row value not showing correct in Matrix

Anonymous — Mon, 09 May 2022 07:33:02 GMT

Thank you for the response. I'm afraid only using SUMX() do not gives me the wished result. its a pretty complex Data model, which probably is the reason why my table keeps messing with me in order to get the correct results.

I wish i could give some sample but the whole thing is build in SSAS and is quite difficult to get sample from.

Re: Row value not showing correct in Matrix

Icey — Mon, 09 May 2022 07:46:28 GMT

Hi Anonymous ,

Could you just decribe the fields used in the Matrix visual and the relationships among them? If there is some dummy data, it would be better. Let's try to reproduce it.

Best Regards,

Icey