topic Re: Matrix - display 1 or -1 values based on a text field in DAX Commands and Tips

Matrix - display 1 or -1 values based on a text field

Anonymous — Tue, 14 May 2024 19:48:00 GMT

I have a matrix displaying a series of columns, with the last column being a count of unique account numbers (summarizing on countd of those account numers)...So each row should have a 1 in that field. However, I would like to be able indicate a -1 if something cancelled. So I have a column that says if something is cancelled and is blank if it isn't. How can I get that COUNTD of account numbers in that row to be -1 for cancelled and 1 for non-cancelled. I tried using IFs but they don't work

Re: Matrix - display 1 or -1 values based on a text field

Learner27 — Wed, 15 May 2024 06:44:22 GMT

Hi have you tried to create a new calculated column in your data table that assigns 1 for non-cancelled accounts and -1 for cancelled accounts.

Re: Matrix - display 1 or -1 values based on a text field

Anonymous — Wed, 15 May 2024 11:15:57 GMT

Yes but that doens't work because there are multiple rows for each account ID in some cases ... IE maybe 3 or 4 regular ones plus the one record that's cancelled, so the matrix sums them, showing 3 positive and -1 negative...I would want only one distinct record for positive and for cancelled

Re: Matrix - display 1 or -1 values based on a text field

Learner27 — Wed, 15 May 2024 12:19:25 GMT

I belive it should work when you have created a calculated column. Could you provide your Sample PBIX file by hiding all the sensitive Data

Re: Matrix - display 1 or -1 values based on a text field

Anonymous — Thu, 16 May 2024 09:01:55 GMT

Hi Anonymous

Thanks for the reply from Learner27 , please allow me to provide another insight:

My sample:

If you don't necessarily want to use a calculated column, try creating the following measure:

Measure = IF(MAX([cancelled]) = BLANK(), CALCULATE(DISTINCTCOUNT('Table'[account]), ALLEXCEPT('Table', 'Table'[account])), -1)

Output:

If this sample data is structurally different from the one you are using, please do not hesitate to mention it and I will modify it.

Best Regards,
Yulia Xu

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