Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

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,

 

2 ACCEPTED SOLUTIONS

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

View solution in original post

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!

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

UsermasterUsermasterIncoming CallIncoming CallOutgoing 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,

 

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

 

 

SivaMani_0-1620829299695.png

 

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

 

Anonymous
Not applicable

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

 

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
Not applicable

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

@Anonymous
Sure. Wish you the best!

@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
Not applicable

Griphon_0-1620828395962.pngGriphon_1-1620828425183.png

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

amitchandak
Super User
Super User

@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"
)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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
Resident Rockstar
Resident Rockstar

@Anonymous,

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.