DAX measure to aggregate only when not exists any single row for condition grouped by a level

[smpa01]

My sample dataset is following

| sn | site  | cond | val  |
|----|-------|------|------|
| 1  | site1 | X    | 100  |
| 2  | site1 | Y    | 200  |
| 3  | site1 | Z    | 300  |
| 1  | site2 | A    | 100  |
| 2  | site2 | B    | 200  |
| 3  | site2 | C    | 300  |
| 1  | site3 | X    | 100  |
| 2  | site3 | P    | 200  |
| 3  | site3 | Q    | 300  |
| 1  | site4 | A    | 100  |
| 2  | site4 | Y    | 200  |
| 3  | site4 | Q    | 300  |
| 1  | site5 | E    | 100  |
| 1  | site5 | E    | 1000 |
| 2  | site5 | F    | 200  |
| 3  | site5 | G    | 300  |

I want DAX to check by site if there exists a row where cond='X' or cond ='Y'. if yes, retrun null, if no, sum val.

My desired result is below

| sn | site  | cond | revisedTotal |
|----|-------|------|--------------|
| 1  | site1 | X    | NULL         |
| 2  | site1 | Y    | NULL         |
| 3  | site1 | Z    | NULL         |
| 1  | site2 | A    | 100          |
| 2  | site2 | B    | 200          |
| 3  | site2 | C    | 300          |
| 1  | site3 | X    | NULL         |
| 2  | site3 | P    | NULL         |
| 3  | site3 | Q    | NULL         |
| 1  | site4 | A    | NULL         |
| 2  | site4 | Y    | NULL         |
| 3  | site4 | Q    | NULL         |
| 1  | site5 | E    | 1100         |
| 2  | site5 | F    | 200          |
| 3  | site5 | G    | 300          |

A TSQL equivalent solution would be following with DDL

declare @t1 as table (sn int, site varchar(max), cond varchar(max), val int)
insert into @t1
select * from (values(1,'site1','X',100),(2,'site1','Y',200),(3,'site1','Z',300)
                     , (1,'site2','A',100),(2,'site2','B',200),(3,'site2','C',300)
					 , (1,'site3','X',100),(2,'site3','P',200),(3,'site3','Q',300)
					 , (1,'site4','A',100),(2,'site4','Y',200),(3,'site4','Q',300)
					 , (1,'site5','E',100),(1,'site5','E',1000), (2,'site5','F',200),(3,'site5','G',300)) t (a,b,c,d)


select a.sn, a.site, a.cond, sum(case when a.site=x.site then a.val else null end) as revisedVal
from @t1 a
outer apply (select b.site from (select site from @t1
where cond <>'X' or cond <>'Y'
EXCEPT
select site from @t1
where cond ='X' or cond ='Y'
) b where a.site=b.site) x
group by  a.site, a.sn, a.cond

How can I achieve the same in a DAX measure?

Queryable source tbl

sn site cond val
1 site1 X 100
2 site1 Y 200
3 site1 Z 300
1 site2 A 100
2 site2 B 200
3 site2 C 300
1 site3 X 100
2 site3 P 200
3 site3 Q 300
1 site4 A 100
2 site4 Y 200
3 site4 Q 300
1 site5 E 100
1 site5 E 1000
2 site5 F 200
3 site5 G 300

I have tried the following DAX measure but it does not return what I was hoping for

Measure =
CALCULATE (
    SUM ( 'fact'[val] ),
    TREATAS (
        EXCEPT (
            SUMMARIZE (
                FILTER ( 'fact', 'fact'[cond] <> "X" || 'fact'[cond] <> "Y" ),
                'fact'[site]
            ),
            SUMMARIZE (
                FILTER ( 'fact', 'fact'[cond] = "X" || 'fact'[cond] = "Y" ),
                'fact'[site]
            )
        ),
        'fact'[site]
    )
)

I want a performant DAX measure that only returns value for site2 and site5 because there does not exist any row for any of these sites where  cond=X or cond =Y by site

Thank you in advance

[CNENFRNL · Accepted solution]

revisedVal = 
SUMX(
    VALUES( table1[site] ),
    IF(
        ISEMPTY(
            CALCULATETABLE(
                table1,
                ALLEXCEPT( table1, table1[site] ),
                table1[cond] IN { "X", "Y" }
            )
        ),
        CALCULATE( SUM( table1[val] ) )
    )
)

[smpa01]

@CNENFRNL @Fowmy  learnt a lot by analyzing this solution.

The key takeway from this how 

'fact'[cond] IN { "X", "Y" }

differs from

FILTER(fact,fact[cond]="X"||fact[comd]"Y")

DAX is beautifully complex and hence, the nuances in syntax makes a world of difference for the meaures even though for table evaluation they return the same result but they are not the same only understood when it comes to use that int he measure.

Also, I learnt the usage of  ISEMPTY with a practical use case. Thanks for that.

Since, I am running the measure on 2M+ row table (constantly compounding) I prefer a solution without iterator, hence after analyzing I rewrote the measure using TREATAS. Also, I wanted to see how can I utilize this inside TREATAS.

noIterator = 
VAR _00 =
    ALLEXCEPT ( 'fact', 'fact'[site] )
VAR _0 =
    SUMMARIZE (
        CALCULATETABLE ( 'fact', 'fact'[cond] IN { "X", "Y" }, _00 ),
        [site]
    )
VAR _1 =
    SUMMARIZE (
        CALCULATETABLE ( 'fact', NOT 'fact'[cond] IN { "X", "Y" }, _00 ),
        [site]
    )
VAR _2 =
    EXCEPT ( _1, _0 )
VAR _3 =
    CALCULATE ( [_sum], TREATAS ( _2, 'fact'[site] ) )
RETURN
    _3

[Fowmy]

@smpa01 

Try this meaure:

M1 = 
VAR __Site = MAX(Table1[ site  ])
VAR __Cond = CALCULATETABLE( VALUES(Table1[ cond ]) , Table1[ site  ] = __Site , REMOVEFILTERS(Table1))

RETURN
IF( 
    ISEMPTY( INTERSECT( {"X","Y"}, __Cond)),
    SUM(Table1[ val  ])
)

Use the following measure if you need the grand total also to be evaluated correctly:

M1 = 
SUMX(
    Table1,
    VAR __Site = Table1[ site  ]
    VAR __Cond = CALCULATETABLE( VALUES(Table1[ cond ]) , Table1[ site  ] = __Site , REMOVEFILTERS(Table1))
    RETURN
    IF( 
        ISEMPTY( INTERSECT( {"X","Y"}, __Cond)),
        Table1[ val  ]
    )
)

[TomMartens]

Hey @smpa01 ,

please provide sample data, that can easily be imported into Power BI Desktop, or create a pbix that contains sample data, upload the pbix to onedrive or dropbox and share the link.

Regards,
Tom

[smpa01]

Hi @TomMartens  it was already provided in the OP