March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
@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
)
Refer to my Video on Switch : https://www.youtube.com/watch?v=gelJWktlR80&pbjreload=101
@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
)
Refer to my Video on Switch : https://www.youtube.com/watch?v=gelJWktlR80&pbjreload=101
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.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
23 | |
16 | |
12 | |
9 | |
7 |
User | Count |
---|---|
38 | |
32 | |
28 | |
12 | |
11 |