topic Re: How to add a column based on a particular value on another column in DAX Commands and Tips

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

Theiren — Thu, 15 Aug 2019 13:04:48 GMT

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.

Any ideas? Thank you!

/Theiren

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

Anonymous — Thu, 15 Aug 2019 13:23:56 GMT

[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

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

Theiren — Thu, 15 Aug 2019 13:37:02 GMT

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

/Theiren

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

Theiren — Thu, 15 Aug 2019 14:21:36 GMT

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

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

Anonymous — Thu, 15 Aug 2019 14:44:45 GMT

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

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

Anonymous — Thu, 15 Aug 2019 14:46:00 GMT

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

Best
Darek

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

Theiren — Fri, 16 Aug 2019 13:25:25 GMT

Thank you Anonymous !!! 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

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

Anonymous — Fri, 16 Aug 2019 14:03:25 GMT

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 :) There's always a way to get what you want.

Best
Darek