topic Re: How to count rows if my measure value is minus ? in DAX Commands and Tips

How to count rows if my measure value is minus ?

Fighting21 — Tue, 29 Oct 2024 04:14:40 GMT

+ I edited question

How can i count rows if my measure value <0

I made measure _CurrentDataValueMPreviousDateValue

_CurrentDateValueMPreviousDateValue =

VAR CurrentDate = MAX('DataTable'[Date])

VAR PreviousDate =

CALCULATE(

MAX('DataTable'[Date]),

FILTER(

ALL('DataTable'),

'DataTable'[Date] < CurrentDate

)

VAR _CurrentDateValue =

CALCULATE(

SUM('DataTable'[Value]),

'DataTable'[Date] = CurrentDate

)

VAR _PreviousDateValue =

CALCULATE(

SUM('DataTable'[Value]),

'DataTable'[Date] = PreviousDate

)

VAR _CurrentDateValueMPreviousDateValue = _CurrentDateValue-_PreviousDateValue

RETURN

_CurrentDateValueMPreviousDateValue

and i try to count rows that _CurrentDataValueMPreviousDateValue is minus

so i tried

Count = Countrows(filter('DataTable',[_CurrentDateValueMPreviousDateValue]<0))

but it's not work result is blank,

how can i count rows if measure value < 0 ?

I posted my example pbix file to could download is https://blog.naver.com/thstjdtn21/223637964699

Re: How to count rows if my measure value is minus ?

MattAllington — Tue, 29 Oct 2024 01:41:59 GMT

You could write this

Countrows(filter(DataTable,Datatable[value]<0))

Re: How to count rows if my measure value is minus ?

Jihwan_Kim — Tue, 29 Oct 2024 03:54:03 GMT

Hi,

Please try something like below if it works.

expected result measure: = VAR _t = FILTER ( DataTable, [_CurrentDataValueMPreviousDateValue] < 0 ) RETURN COUNTROWS ( _t )

Re: How to count rows if my measure value is minus ?

Fighting21 — Tue, 29 Oct 2024 05:42:08 GMT

Sorry, i changed my question.

Re: How to count rows if my measure value is minus ?

Fighting21 — Tue, 29 Oct 2024 05:42:27 GMT

Sorry, i changed my question.

Re: How to count rows if my measure value is minus ?

shafiz_p — Tue, 29 Oct 2024 06:19:13 GMT

Hi Fighting21

You raw data don't have negative and after summarization, measure have negative. So, I think you need to summarize first and then compare with measure and count rows.

You can try the below code, if it is meets your goal:

CountNegetive = VAR SummarizedTable = SUMMARIZE( 'DataTable', 'DataTable'[Category], 'DataTable'[SubCategory], 'DataTable'[Date], "TotalValue", SUM('DataTable'[Value]) ) VAR CountNegativeRows = SUMX( SummarizedTable, IF( [_CurrentDateValueMPreviousDateValue] < 0, 1, 0 ) ) RETURN CountNegativeRows

I found this result, though I don't know it is the desired result what you want to achieve or not:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

Re: How to count rows if my measure value is minus ?

Fighting21 — Tue, 29 Oct 2024 09:01:23 GMT

Thank you very much