If clause logic

[Anonymous]

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.

[parry2k · Accepted solution]

@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.

[v-yetao1-msft · Accepted solution]

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.

[parry2k]

@Anonymous not sure what you mean by performance issue, it shouldn't be. What made you think it will be a performance issue?

[parry2k · Accepted solution]

@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.

[Anonymous]

Hi @parry2k ,

Is it possible with if clause i don't want to use variable due to some performance issue