topic Re: Unsolvable DAX conundrum in Matrix Visual? in DAX Commands and Tips

Unsolvable DAX conundrum in Matrix Visual?

zazaalaza — Thu, 04 Jan 2024 15:54:13 GMT

Hi,

I have a matrix visual that is made from 2 unrelated tables and measures that executes against those 2 tables.

Test Measures:

Positive = IF ( SELECTEDVALUE ( 'Columns'[Value] ) = SELECTEDVALUE ( 'Rows'[Value] ), BLANK(), RANDBETWEEN ( 10, 100 ) )

Negative = IF ( SELECTEDVALUE ( 'Columns'[Value] ) = SELECTEDVALUE ( 'Rows'[Value] ), BLANK(), RANDBETWEEN ( 10, 100 ) * - 1 )

What I would like to do is to merge the 2 measures such that the positive values display above the diagonal line and the negative values are displayed below the negative values.

I am having problems even approaching the issue, here is the powerbi file for reference: https://file.io/ZPVaVYkPmMrz

Would really appreciate if someone could point me in the right direction.

Re: Unsolvable DAX conundrum in Matrix Visual?

AlexisOlson — Thu, 04 Jan 2024 16:25:56 GMT

Try this:

VAR _Row = SELECTEDVALUE ( 'Rows'[Value] ) VAR _Col = SELECTEDVALUE ( 'Columns'[Value] ) RETURN SWITCH ( TRUE (), _Row < _Col, RANDBETWEEN ( 10, 100 ), _Row > _Col, RANDBETWEEN ( 10, 100 ) * -1 )

Re: Unsolvable DAX conundrum in Matrix Visual?

zazaalaza — Thu, 04 Jan 2024 16:50:19 GMT

The totals are reversed, Should also be negative and positive 🙂 But cool, this already works

Re: Unsolvable DAX conundrum in Matrix Visual?

AlexisOlson — Thu, 04 Jan 2024 17:04:25 GMT

That's because the row/column value is blank for those, which is treated like a zero when compared to 1-10.

I don't know what totals actually make sense in your scenario, so I didn't attempt to adjust them.