Forum Discussion
If clause logic
Hi All,
I have an use case where i have to create 2 calculated columns named to report and not to report.Below is the logic defined for those columns.
To report=
Case 1:if telecom/non telecom field is telecom and both the country A and country B is "GAZ"then i have to populate the total USD Value
Case 2: if telecom/non telecom field is telecom , country A is "GAZ"and country B not "GAZ"then i have to populate the 50% of total USD Value
Case 3: if telecom/non telecom field is telecom , country A is not "GAZ"and country B = "GAZ"then i have to populate the 50% of total USD Value
Not to report=
Case 1: if telecom/non telecom field is telecom , country A is "GAZ"and country B not "GAZ"then i have to populate the 50% of total USD Value
Case 2: if telecom/non telecom field is telecom , country A is not "GAZ"and country B = "GAZ"then i have to populate the 50% of total USD Value
Please find the sample data and output for reference
Can someone help me to arrive this logic.
Thanks in advance.
Anonymous here is how you can add, you can tweak it as you see fit
To Report - VAR __telecom = Table[Telecom/NonTelecom] VAR __countryA = Table[CountryA] VAR __countryB = Table[CountryB] VAR __usd = Table[USD] RETURN SWITCH ( TRUE(), __telecom = "Telecom" && __countryA = "GAZ" && __countryB = "GAZ", __usd, __telecom = "Telecom" && __countryA = "GAZ" && __countryB <> "GAZ", __usd * 0.5, __telecom = "Telecom" && __countryA <> "GAZ" && __countryB = "GAZ", __usd * 0.5, 0 --this is else condition )similarly, you can add not to report column
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
Hi Anonymous
Create two calculated columns to return the value .
To report = SWITCH(TRUE(),'Table'[Telecom/Non telecom]="telecom" && 'Table'[Country A]="GAZ" && 'Table'[Country B]="GAZ",'Table'[USD], 'Table'[Telecom/Non telecom]="telecom" && 'Table'[Country A]="GAZ" && 'Table'[Country B]<>"GAZ",'Table'[USD]*0.5, 'Table'[Telecom/Non telecom]="telecom" && 'Table'[Country A]<>"GAZ" && 'Table'[Country B]="GAZ",'Table'[USD]*0.5)Not to report = SWITCH(TRUE(),'Table'[Telecom/Non telecom]="telecom" && 'Table'[Country A]="GAZ" && 'Table'[Country B]<>"GAZ",'Table'[USD]*0.5, 'Table'[Telecom/Non telecom]="telecom" && 'Table'[Country A]<>"GAZ" && 'Table'[Country B]="GAZ",'Table'[USD]*0.5)Then add the two columns in table .The final result is as shown :
I have attached my pbix file, you can refer to it .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous here is how you can add, you can tweak it as you see fit
To Report - VAR __telecom = Table[Telecom/NonTelecom] VAR __countryA = Table[CountryA] VAR __countryB = Table[CountryB] VAR __usd = Table[USD] RETURN SWITCH ( TRUE(), __telecom = "Telecom" && __countryA = "GAZ" && __countryB = "GAZ", __usd, __telecom = "Telecom" && __countryA = "GAZ" && __countryB <> "GAZ", __usd * 0.5, __telecom = "Telecom" && __countryA <> "GAZ" && __countryB = "GAZ", __usd * 0.5, 0 --this is else condition )similarly, you can add not to report column
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
Hi parry2k ,
Is it possible with if clause i don't want to use variable due to some performance issue
Anonymous not sure what you mean by performance issue, it shouldn't be. What made you think it will be a performance issue?
Hi Anonymous
Create two calculated columns to return the value .
To report = SWITCH(TRUE(),'Table'[Telecom/Non telecom]="telecom" && 'Table'[Country A]="GAZ" && 'Table'[Country B]="GAZ",'Table'[USD], 'Table'[Telecom/Non telecom]="telecom" && 'Table'[Country A]="GAZ" && 'Table'[Country B]<>"GAZ",'Table'[USD]*0.5, 'Table'[Telecom/Non telecom]="telecom" && 'Table'[Country A]<>"GAZ" && 'Table'[Country B]="GAZ",'Table'[USD]*0.5)Not to report = SWITCH(TRUE(),'Table'[Telecom/Non telecom]="telecom" && 'Table'[Country A]="GAZ" && 'Table'[Country B]<>"GAZ",'Table'[USD]*0.5, 'Table'[Telecom/Non telecom]="telecom" && 'Table'[Country A]<>"GAZ" && 'Table'[Country B]="GAZ",'Table'[USD]*0.5)Then add the two columns in table .The final result is as shown :
I have attached my pbix file, you can refer to it .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.