cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Multiple Case Statement in a measure

Hi I need help with creating a measure for below case statement. I am using direct query so unable to create a calculate column.

sum(case when Table1[field1] in (10, 12, 13, 33, 35, 37, 39) THEN 0 when Table1[field1] in (71, 75) THEN Table1[amt] when Table2[name] = 'AB' THEN [amt] else 0 end) as 'Gross'

Appreciate if you could help with getting the measure for the above case statement

1 ACCEPTED SOLUTION
Super User

@Anonymous , You can try like this. But You can not simply use Table 2  in table 1. That will depend on relation .

sumX(Table1 , Switch(True(), Table1[field1] in {10, 12, 13, 33, 35, 37, 39 } , 0 ,
Table1[field1] in {71, 75} , Table1[amt] ,
Table1[name] = "AB" , Table1[amt] , 0
)

or

sumX(Table1 , Switch(True(), Table1[field1] in {10, 12, 13, 33, 35, 37, 39 } , 0 ,
Table1[field1] in {71, 75} , Table1[amt] ,
related(Table2[name]) = "AB" , related(Table2[amt]) , 0
)

2 REPLIES 2
Super User

@Anonymous , You can try like this. But You can not simply use Table 2  in table 1. That will depend on relation .

sumX(Table1 , Switch(True(), Table1[field1] in {10, 12, 13, 33, 35, 37, 39 } , 0 ,
Table1[field1] in {71, 75} , Table1[amt] ,
Table1[name] = "AB" , Table1[amt] , 0
)

or

sumX(Table1 , Switch(True(), Table1[field1] in {10, 12, 13, 33, 35, 37, 39 } , 0 ,
Table1[field1] in {71, 75} , Table1[amt] ,
related(Table2[name]) = "AB" , related(Table2[amt]) , 0
)

Super User

You can use SWITCH(TRUE() for a case

Gross =

SWITCH(TRUE()

, SELECTEDVALUE(Table1[field1]) in (10, 12, 13, 33, 35, 37, 39) , 0

, SELECTEDVALUE(Table1[field1]) in (71, 75) , SUM(Table1[amt])

, SELECTEDVALUE(Table2[name]) = 'AB' , SUM([amt])

, 0 )

@Anonymous note it doesn't provide the row context, so you'll still need to provide that in the visual as you're using direct query.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors