topic Re: Avoiding displaying duplicate values for parent child items in a matrix in DAX Commands and Tips

Avoiding displaying duplicate values for parent child items in a matrix

tnwcloud — Sat, 03 Aug 2024 20:21:49 GMT

I have a dataset that includes goals and multiple options for each goal. Each goal has a target value and each option has an estimate. There is a one to many relationship between goal and option. Trying to figure out how not to repeat the 'Goal Target' for each Option in the simplified matrix below. Basically I want the opposite of Don't Show Subtotals, where I show the Subtotal of 'Goal Target' for each Goal but don't show the lime item for each Option. Putting this under DAX but would welcome data model or visual option solutions.

Can't figure out how to upload a file so...

Re: Avoiding displaying duplicate values for parent child items in a matrix

DataInsights — Sat, 03 Aug 2024 21:37:40 GMT

tnwcloud,

The May 2024 release of Power BI Desktop contains matrix enhancements including various layout options. Here's a link with details:

https://powerbi.microsoft.com/en-us/blog/power-bi-may-2024-feature-summary/#post-27048-_Toc167109007

Re: Avoiding displaying duplicate values for parent child items in a matrix

Anonymous — Mon, 05 Aug 2024 08:41:29 GMT

Hi tnwcloud

Please try the following measure:

Goal Target = IF( NOT(HASONEVALUE('Options'[OptionID])), CALCULATE( SUM('Goals'[Target]), CROSSFILTER('Goals'[GoalID], 'Options'[GoalID], Both) ), BLANK() )

Result:

Best Regards,

Jayleny

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