topic Re: count all negative numbers in DAX Commands and Tips

count all negative numbers

Nick_2510 — Wed, 11 May 2022 10:42:33 GMT

I want to count all negative numbers by rows and in total...but it doesn't work in total

M =

SUMX ( VALUES ( Data[Name] ), IF ( [diff_AvSal_bal] < -1, 1, 0))

Re: count all negative numbers

tamerj1 — Wed, 11 May 2022 10:52:23 GMT

Hi Nick_2510

Please try

Count of -ve = SUMX ( VALUES ( Data[Name] ), CALCULATE ( IF ( [diff_AvSal_bal] < -1, 1, 0 ) ) )

Re: count all negative numbers

Nick_2510 — Wed, 11 May 2022 10:57:03 GMT

Hi! The same result(

Re: count all negative numbers

Arul — Wed, 11 May 2022 10:58:25 GMT

Nick_2510 ,

try this,

Negative val = SUMX(Negative,IF(Negative[Diff]<0,1,0))

Re: count all negative numbers

Nick_2510 — Wed, 11 May 2022 11:00:21 GMT

This formula is not for measures

Re: count all negative numbers

tamerj1 — Wed, 11 May 2022 11:30:05 GMT

Nick_2510
Do you have other active filters of the page or on the visual? any slicers? Any other table or other column invloved?

Re: count all negative numbers

tamerj1 — Wed, 11 May 2022 11:34:18 GMT

Nick_2510
Please try

Count of -ve = SUMX ( CROSSJOIN ( VALUES ( 'Date'[Date] ), VALUES ( Data[Name] ) ), IF ( [diff_AvSal_bal] < -1, 1, 0 ) )

Re: count all negative numbers

Nick_2510 — Fri, 13 May 2022 06:41:25 GMT

Thx a lot! It works!

But now I need to add one option in IF constraction...

switch off name if name in a period - data for this in another table with colomns (Name, Start_period, End_Period)

Count of -ve =

SUMX (

CROSSJOIN ( VALUES ('Календарь'[Date]), VALUES ( Data[Name] ) ),

IF ( [diff_AvSal_bal] >0 OR 'Календарь'[Date] IN DATESBETWEEN('Sheet1'[Start_per], 'Sheet1'[End_per], 0, 1)

)

Re: count all negative numbers

Nick_2510 — Fri, 13 May 2022 07:36:06 GMT

SUMX (

CROSSJOIN ( VALUES ('Календарь'[Date]), VALUES ( Data[Name] ), 'delist'),

IF ( [diff_AvSal_bal] >= 0 || ('Календарь'[Date] >= 'delist'[Start_per] && 'Календарь'[Date] <= 'delist'[End_per]), 0, 1 )

)

How to connect Name in first table and in Second (Start_per, End_per)...now it puts 0 to every name which is in that period(

Re: count all negative numbers

tamerj1 — Fri, 13 May 2022 08:14:14 GMT

Nick_2510
Now its different. I need to see sample data of the two tables and the relationships between the three tables.

Re: count all negative numbers

Nick_2510 — Fri, 13 May 2022 09:35:25 GMT

tamerj1

delist

data

relationships

other 2 tables are handbooks....Glossary - names of products and specifications, and Celendar(Date)

Re: count all negative numbers

tamerj1 — Fri, 13 May 2022 09:57:06 GMT

Nick_2510
In your table visual please use the [Name] column from the 'delist' dimention table and not from the fact 'Data' table. Then you may try

Count of -ve = SUMX ( CROSSJOIN ( VALUES ( 'Календарь'[Date] ), delist ), IF ( [diff_AvSal_bal] > 0 || 'Календарь'[Date] IN CALENDAR ( delist[Start_per], delist[End_per] ), 0, 1 ) )

Re: count all negative numbers

Nick_2510 — Fri, 13 May 2022 11:45:37 GMT

But in table 'delist' are not all names...the main table is Data...all calculations and visualizations are made due to it...and Name I take from Glossary...