cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Theiren Frequent Visitor
Frequent Visitor

How to add a column based on a particular value on another column

Ok, here is the problem, I think this is easy but just didn't figure out the solution by myself.

 

I have two tables, which are related by Object. My problem is TABLE 1.

In TABLE 1 the Object can get four kind of status:

 

1. Applied

2. Viewed

3. Approved OR Rejected

 

I would like to add a column to TABLE 1 (green RESULT column below) which gives the number 3. result for all rows within the same Object. So when I select an object in visualization, it gives me immediately the information if the Object is Approved or Rejected. I could also use that as a filter to search rejected objects.

 

AddColumn.PNG

Any ideas? Thank you!

 

/Theiren

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to add a column based on a particular value on another column

This is the correct code. Your code should be avoided because it's using CALCULATE in a calculated column and this - as is known - generates a lot of context transitions that slow down the code.

 

[Final Result] = -- calculated column
var __object = T1[Object]
var __hasBeenApproved =
	NOT ISEMPTY(
		FILTER (
			T1,
			T1[Object] = __object
			&&
			T1[Result] = "approved"
		)
	)
retun
	if(
		__hasBeenApproved,
		"Approved",
		"Rejected"
	)

Best

Darek

7 REPLIES 7
Super User
Super User

Re: How to add a column based on a particular value on another column

[Final Result] = -- calculated column
var __object = T1[Object]
var __hasBeenApproved =
	NOT ISEMPTY(
		T,
		T1[Object] = __object
		&&
		T1[Result] = "approved"
	)
retun
	if(
		__hasBeenApproved,
		"Approved",
		"Rejected"
	)

Best

D.

Theiren Frequent Visitor
Frequent Visitor

Re: How to add a column based on a particular value on another column

Thank you for your swift reply @darlove ! Function ISEMPTY seems to take only "Table" as a parameter, is there a typo in your dax?

 

/Theiren

Theiren Frequent Visitor
Frequent Visitor

Re: How to add a column based on a particular value on another column

I created the following DAX:

 

ResultColumn = 
var approved =
    CALCULATE(COUNTROWS(TABLE1),
    FILTER(TABLE1, EARLIER(TABLE1[OBJECT]) = TABLE1[OBJECT] && TABLE1[STAT] = "I6789"))
return
    IF(approved > 0, "Approved", "Rejected")

This gives me the result I was hoping to get. BUT, I am still wondering how to create a slicer of this (i.e. if I want to see only rejected objects). Anyway, I will close this thread now and maybe return the second issue later if needed.

/Theiren

Super User
Super User

Re: How to add a column based on a particular value on another column

This is the correct code. Your code should be avoided because it's using CALCULATE in a calculated column and this - as is known - generates a lot of context transitions that slow down the code.

 

[Final Result] = -- calculated column
var __object = T1[Object]
var __hasBeenApproved =
	NOT ISEMPTY(
		FILTER (
			T1,
			T1[Object] = __object
			&&
			T1[Result] = "approved"
		)
	)
retun
	if(
		__hasBeenApproved,
		"Approved",
		"Rejected"
	)

Best

Darek

Super User
Super User

Re: How to add a column based on a particular value on another column

How to see only the rejected objects? That's simple when you have the column. You just put it in a slicer.

Best
Darek
Theiren Frequent Visitor
Frequent Visitor

Re: How to add a column based on a particular value on another column

Thank you @darlove !!! It works now and I marked your reply as a solution. I am not prof enough to understand which actions slow down the code.

 

I also solved the slicer problem. For some reason if I used this new column as a slicer, it didn't work how I wanted (with info from other table), but when I brought the result to another table (where is only one object per row) it started to work. Thank you anyway! :-)

 

/Theiren

Super User
Super User

Re: How to add a column based on a particular value on another column

Glad it has worked.

__DAX is simple but is NOT easy__. There are many caveats and intricacies that will put people in difficult and hard-to-understand situations very often (unless they know exactly how DAX works) but there's always, ALWAYS, a logical explanation of what you see and it follows the rules to the very last letter.

Be prepared for that but do not get discouraged Smiley Happy There's always a way to get what you want.

Best
Darek

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 307 members 3,259 guests
Please welcome our newest community members: