count all negative numbers

[Nick_2510]

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))

[tamerj1 · Accepted solution]

@Nick_2510 
Please try

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

[Nick_2510]

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)

)

[Nick_2510]

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(

[tamerj1]

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

[Nick_2510]

@tamerj1 

delist

data

relationships

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

[tamerj1]

@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
    )
)

[Nick_2510]

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...

[tamerj1]

Can you share a sample file?

[Nick_2510]

https://drive.google.com/file/d/1HU_modgMtIDKc4aiaRFS_CnshJbe3sxq/view?usp=sharing

[tamerj1]

@Nick_2510 
Sorry I was confused and replied to your other post 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/IF-Conditions/m-p/2512570#M69732

[Nick_2510]

HI @tamerj1 

It should be 0, because this names on this dates are  in delist table (second condition)....(((

[tamerj1]

@Nick_2510 

Maybe you mean "AND" not "OR". Please try replace || with &&

[Nick_2510]

@tamerj1 
No, I mean put 0 if name on this day is in delist OR diff_AvSal_bal > 0...but it takes 1...I tried to reverse conditions and got error

OOS =

SUMX (

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

IF ('Календарь'[Date] IN

CALENDAR (

CALCULATE ( SELECTEDVALUE ( delist[Start_per] ) ),

CALCULATE ( SELECTEDVALUE ( delist[End_per] ) )

||

[diff_AvSal_bal] > 0

),

0, 1 )

)

Calendar start date or end date cannot be null

[tamerj1]

Hi @Nick_2510 

The 'delist' table is not there in the file!

[Nick_2510]

@tamerj1 

Sorry, not this link

https://drive.google.com/file/d/13O4IUGHpTJvEkKrokc5JCie0l1gieKFO/view?usp=sharing

[Arul]

@Nick_2510 ,

try this,

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

[Nick_2510]

This formula is not for measures

[tamerj1]

Hi @Nick_2510 

Please try

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

[Nick_2510]

Hi! The same result(

[tamerj1]

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