Creating new column / table values based on several conditions

[skype_see4d]

Hello,
 
I am relatively new to Power BI. I got one problem I am trying to solve for a day. I gave up.
 
I have a table with some values - se below. I am trying to write a function to: fill new column with boolean values based on following conditions:
if Nr. has status "true and false" than the value is "True",
if Nr. only has status False then the value in new column is "False".
if Nr. only has status True then the value in new column is "True".
 
So 90001= True, 90002= True, 90002= False,   90004= True, 
 
Should I create new column or write this data in the new table with Distinct values for Nr.?
 
Can someone here please point me to the right direction. Thank you.
Matt

Sample data: 

Nr.; Description; Status
90001 NameXVY FALSE
90001 NameXVY FALSE
90001 NameXVY TRUE
90001 NameXVY FALSE
90001 NameXVY FALSE
90001 NameXVY TRUE
90001 NameXVY FALSE
90001 NameXVY FALSE
90002 Name2 FALSE
90002 Name2 FALSE
90002 Name2 TRUE
90002 Name2 FALSE
90003 Name3 FALSE
90003 Name3 FALSE
90003 Name3 FALSE
90004 New name1 TRUE
90004 New name1 TRUE
90004 New name1 TRUE

[KarthikSridaran]

Hello, 

The measure that Matt gave works fine,

Other solution:

IF the column Status is a string like "Yes" or "No" kind of values like below

Nr	Desc	Status1
90001	Name1	Yes
90001	Name1	Yes
90001	Name1	Yes
90001	Name1	No
90001	Name1	No
90001	Name1	No
90001	Name1	No
90001	Name1	No
90002	Name2	Yes
90002	Name2	No
90002	Name2	No
90002	Name2	No
90003	Name3	No
90003	Name3	No
90003	Name3	No
90004	Name4	Yes
90004	Name4	Yes
90004	Name4	Yes

Then do the following

measure : CALCULATE(COUNTA(Sheet1[Nr]),Sheet1[Status1]="Yes")

column :  IF([Measure]>=1,"True",IF(ISBLANK([Measure]),"False"))

You get the following

90001	Name1	TRUE
90002	Name2	TRUE
90003	Name3	FALSE
90004	Name4	TRUE

[MattAllington]

It's not clear why you want to do this, but it is probably best not to use a calc column. Read my article about this here http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

try this measure. 

= if(lastnonblank(values(table[status]),1)=True,True(),False())

[skype_see4d]

Matt,

Thank you for your reply and a solution. It works as a measure, but as you mentioned in your article it can not be used for filtering with slicers.  As you see in the image I get the result, but I can not set a slicer for true- false values.

I must now put this measure into column :). Any idea how to create column from the measured values?

 Best regards

Matt

[v-yuezhe-msft]

Hi @skype_see4d,

What result do you want to get after you select False value in a slicer? If you what to return all Nr. that have FALSE status, then just create a slicer using Status field, then use it to filter your visual.

If you want to return only 90003 that has FALSE status after selecting False value, use the following formula to create the Matts measure, then use Visual Level filter instead of slicer to filter your visual as shown in the following screenshot.

Matts measure = if(lastnonblank(VALUES(Table1[Status]),1)=TRUE,"True","False")

Thanks,
Lydia Zhang

[MattAllington]

@skype_see4d sorry for my slow reply.  I like Lidia's approach if that works for you.  If you want a slicer, do the following.

1.  Create a lookup table of all the ID and Names

2.  Join the new table to the existing table

3.  Delete the Description from the Existing table (no longer needed).

4.  either add the measure as a new calc column (ie = [measures]) or add the following formula

= CALCULATE(if(lastnonblank(VALUES(Data[Nr Description Status.3]),1)="True","True","False"))

Here is a demo

https://www.dropbox.com/s/o7yu7nozm781gxf/returning%20true%20or%20false.xlsx?dl=0