Need help in DAX

[Saranya_Tamil]

I need to figure out whether the Deal is a S4U or not. The logic is if other than S4U in 'Name' field has S4U as family then it is a S4U deal. For ex, here 1 is not S4U bcz other than S4U no one has S4U as family and  2 is S4U deal because ERP in this has S4U family. I don’t know how to achieve this in power BI using DAX.

Deal	Name	Family	Output
1	ERP	ERP	No
1	S4U	S4U	No
1	ERP	MS	No
1	ERP	PS	No
2	ERP	ERP	Yes
2	S4U	S4U	Yes
2	ERP	S4U	Yes
2	ERP	PS	Yes

Need the output as mentioned

[rsbin]

@Saranya_Tamil ,

Please try this as a Calculated Column:

S4UDeal = IF( AND( [Name] <> "S4U", [Family] = "S4U" ), "Yes", "No" )

Trust this will work for you.

Regards,

[Saranya_Tamil]

Many thanks, but I need the output as below

Deal	Name	Family	Output
1	ERP	ERP	No
1	S4U	S4U	No
1	ERP	MS	No
1	ERP	PS	No
2	ERP	ERP	Yes
2	S4U	S4U	Yes
2	ERP	S4U	Yes
2	ERP	PS	Yes

[rsbin · Accepted solution]

@Saranya_Tamil ,

Add this as a second Calculated Column:

Output = SWITCH(
             TRUE(),
             CALCULATE( SUM([S4UDeal] ),
                  ALLEXCEPT( 'Deals (2)', 'Deals (2)'[Deal] )) > 0, "Yes",
                  "No" )

If you want, you can probably combine both into just one column.

Regards,

[Saranya_Tamil]

Thank you @rsbin , it works 🙂

[Ttaylor9870]

Hi @Saranya_Tamil ,

You need to create a new calculated column and this should hopefully do the job...

S4U Deal = IF('Table'[Name] = "S4U",'Table'[Family],'Table'[Name])

'Table' in the DAX is the name of your Table, let me know if this worked or not. 😊

Many Thanks,

Taylor