Forum Discussion
If and else linked data sources
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,
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.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!
11 Replies
Anonymous,
To help you with the right solution, please post your model and table structure with sample data (if possible).
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"
)- 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])returnSwitch( True() ,not(isblank(_1)), "Outgoing",not(isblank(_2)), "Incoming")It doesnt like the code marked in red.
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
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.I receive this error? Thank you for your help thus far. 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
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".
