Forum Discussion
Creating new column / table values based on several conditions
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
5 Replies
- MattAllington
Community Champion
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())
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
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
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
