Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nick_2510
Helper I
Helper I

IF Conditions

Hi everyone!

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

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

 

working constration:

 

Count of -ve =

SUMX (
CROSSJOIN ( VALUES ('Календарь'[Date]), VALUES ( Data[Name] ) ),
IF ( [diff_AvSal_bal] < -1, 1, 0 )
)

I try to add condition  in IF constraction, but it doesnt work

 

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


1 ACCEPTED SOLUTION

@Nick_2510 
Please try

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

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

@Nick_2510 
Here you go https://we.tl/t-j2udW5gRf0

OOS = 
SUMX (
    CROSSJOIN ( VALUES ('Календарь'[Date]), VALUES ( Glossary[Name] ) ),
    IF ( [diff_AvSal_bal] < -1 
        || 'Календарь'[Date] IN 
        CALENDAR ( 
            CALCULATE ( SELECTEDVALUE ( delist[Start_per] ) ), 
            CALCULATE ( SELECTEDVALUE ( delist[End_per] ) ) 
        ),
    1, 0 )
)

  

@Nick_2510 
This one is the latest solution I shared. Please download the file and check

tamerj1
Super User
Super User

Hi @Nick_2510 
Please try

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

@tamerj1 

It almost works, but the last month (Apr 2022) doesnt calculate...in total(....it is really strange...because with other months everything is OK

Nick_2510_0-1652698020900.png

 

@Nick_2510 
Please try

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

@Nick_2510 
One possiblity is having blank start dates. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors