If and else linked data sources

[Anonymous]

Good Day,

I was hoping someone could assist me in a very simple(to you) query with a result return.

Scenario: I have two main columns that will contain a log of information. The one column would be Source and the second column would be destination. There is also a Master table with static information.

My question is that I want a column in PowerBI to display text based on the query result i.e.: Incoming or Outgoing.

if Data([Source], "Master[Extension]") then "Outgoing" else "Incoming"

or have two seperate columns?

if Data([Source], "Master[Extension]") then "Outgoing"
if Data([Destination], "Master[Extension]") then "Incoming"

Any assitance would be greatly appreciated,

Thank you,

[SivaMani · Accepted solution]

@Anonymous, it helps.

Try the below DAX code in a calculated column.

Type = SWITCH(TRUE(),'Table'[Source] IN VALUES(UserMaster[User extension]),"Outgoing", 'Table'[Destination] IN VALUES(UserMaster[User extension]),"Incoming")

Note: You have to change the Table names as per your model.

[Anonymous]

UsermasterIncoming CallOutgoing Call

If a extension number exsits in the Usermaster and is displayed in the desination column then it would be marked as "Incoming" and if a extension number exsits in the source column then it would be "Outgoing", but if an extension matches in both columns it would be "Internal".

Please let me know if this helps? @SivaMani 

[SivaMani]

@Anonymous,

You are using measure and it won't work for this code. Use the calculated column.

Refer this documentation: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns

[Anonymous]

@SivaMani Wow, you are a Super Star.... One last question, if an extension matches the Master table and in both Data"Source" and Data"Destination" how can I make it "Internal".

[SivaMani · Accepted solution]

Try this, 

Type = SWITCH(TRUE(),'Table'[Source] IN VALUES(UserMaster[User extension]) && 'Table'[Destination] IN VALUES(UserMaster[User extension]),"Internal",'Table'[Source] IN VALUES(UserMaster[User extension]),"Outgoing", 'Table'[Destination] IN VALUES(UserMaster[User extension]),"Incoming")

I am glad that It worked. Please mark it as Solution. Appreciate your kudos!

[Anonymous]

@SivaMani , Legend. Thank you. I have a whole bunch of other questions but let me try myself first prior to just posting on the forums. Thank you again.

[SivaMani]

@Anonymous
Sure. Wish you the best!

[SivaMani · Accepted solution]

@Anonymous, it helps.

Try the below DAX code in a calculated column.

Type = SWITCH(TRUE(),'Table'[Source] IN VALUES(UserMaster[User extension]),"Outgoing", 'Table'[Destination] IN VALUES(UserMaster[User extension]),"Incoming")

Note: You have to change the Table names as per your model.

[Anonymous]

I receive this error? Thank you for your help thus far. @SivaMani 

[amitchandak]

@Anonymous , Try a new column like this in DAX

new column
var _1 countx(filter(Master, Master[Extension] =Data[Source]),Data[Source])
var _2 countx(filter(Master, Master[Destination] =Data[Source]),Data[Source])
return
Switch( True() ,
not(isblank(_1)), "Outgoing",
not(isblank(_2)), "Incoming"
)

[Anonymous]

InOut =

var _1 countx(filter(UserMaster, UserMaster[User Extension] =Data[Source]),Data[Source])

var _2 countx(filter(UserMaster, UserMaster[User extension] =Data[Source]),Data[Source])

return

Switch( True() ,

not(isblank(_1)), "Outgoing",

not(isblank(_2)), "Incoming"

)

It doesnt like the code marked in red.

[SivaMani]

@Anonymous,

To help you with the right solution, please post your model and table structure with sample data (if possible).