DAX 'IF' question

[2avrham]

Hi all,

I having issue to "search" for specific text when I use IF faction at Dax.

I have the next code:
=IF([TesterName]="G1", IF([status] = 'Active',[status],[status]))


that should search for "G1" under "TesterName" column.

My problem is that at Testername column data is XXXXG1, CCCCCG1 and I don't now how to edit it to take those cells too (to ignore al text data from the beginning like '%G1" at SQL.

thanks!!

 

[oldbrewer]

show us a few rows of data as it arrives from the SQL query and show us your desired output

 

[2avrham]

MTDate	Weekday	Non PrimeTime	Non Prime Time hour	TesterName
1/27/2017 3:45:00 AM	6	0	3	CMT0289
1/27/2017 3:45:00 AM	6	0	3	CMT0062G1
1/27/2017 3:45:00 AM	6	0	3	CMT0062
1/27/2017 3:45:00 AM	6	0	3	CMT0289G1
1/27/2017 3:30:00 AM	6	0	3	CMT0289
1/27/2017 3:30:00 AM	6	0	3	CMT0062G1
1/27/2017 3:30:00 AM	6	0	3	CMT0062
1/27/2017 3:30:00 AM	6	0	3	CMT0289G1
1/27/2017 3:15:00 AM	6	0	3	CMT0062G1
1/27/2017 3:15:00 AM	6	0	3	CMT0289
1/27/2017 3:15:00 AM	6	0	3	CMT0062
1/27/2017 3:15:00 AM	6	0	3	CMT0289G1
1/27/2017 3:00:00 AM	6	0	3	CMT0289
1/27/2017 3:00:00 AM	6	0	3	CMT0062G1
1/27/2017 3:00:00 AM	6	0	3	CMT0062
1/27/2017 3:00:00 AM	6	0	3	CMT0289G1

<tbody>
</tbody>

 

[Norie]

Where's the 'status' column?

Also, what do you want to return if 'G1' isn't found in column 'TesterName'?

 

[Norie]

This will return 'Yes' if 'G1' is found in the TesterName column and 'No' if it isn't.

= IF(ISERROR(SEARCH("G1",Table1[TesterName])), "No", "Yes")

 

[2avrham]

I saw that I miss something here, I attach all data + explanation for what im hope to retrieve.

MTDate	Weekday	Non PrimeTime	Non Prime Time hour	TesterName	Day	Login	TesterStatus	TesterState	status
1/26/2017 23:15	5	1	23	CMT0748G1	26		3	8	Logged Out
1/26/2017 23:30	5	1	23	CMT0748G1	26		3	8	Logged Out
1/26/2017 23:15	5	1	23	CMT0289G1	26		3	8	Logged Out
1/26/2017 23:30	5	1	23	CMT0289G1	26		3	8	Logged Out
1/26/2017 23:15	5	1	23	CMT0395G1	26		3	8	Logged Out
1/26/2017 23:30	5	1	23	CMT0395G1	26		3	8	Logged Out
1/26/2017 23:15	5	1	23	CMT0441G1	26		3	8	Logged Out
1/26/2017 23:30	5	1	23	CMT0441G1	26		3	8	Logged Out
1/26/2017 23:15	5	1	23	CMT0628G1	26		3	8	Logged Out
1/26/2017 23:30	5	1	23	CMT0628G1	26		3	8	Logged Out
1/26/2017 23:15	5	1	23	CMT0625G1	26		3	8	Logged Out
1/26/2017 23:30	5	1	23	CMT0625G1	26		3	8	Logged Out
1/26/2017 23:15	5	1	23	CMT0327G1	26		3	8	Logged Out
1/26/2017 23:30	5	1	23	CMT0327G1	26		3	8	Logged Out
1/26/2017 23:15	5	1	23	CMT0291G1	26		7	8	Down
1/26/2017 23:30	5	1	23	CMT0291G1	26		7	8	Down
1/26/2017 23:15	5	1	23	CMT0534	26		2	1	Inactive
1/26/2017 23:30	5	1	23	CMT0534	26		2	1	Inactive
1/26/2017 23:15	5	1	23	CMT0534G1	26		2	1	Inactive
1/26/2017 23:30	5	1	23	CMT0534G1	26		2	1	Inactive
1/26/2017 23:15	5	1	23	CMT0289	26	ekuprian	1	2	Active
1/26/2017 23:30	5	1	23	CMT0441	26	ekuprian	1	3	Active
1/26/2017 23:30	5	1	23	CMT0291	26	ekuprian	1	3	Active
1/26/2017 23:15	5	1	23	CMT0291	26	ekuprian	1	3	Active
1/26/2017 23:30	5	1	23	CMT0395	26	Not Detected	1	3	Active
1/26/2017 23:15	5	1	23	CMT0327	26	ekuprian	1	3	Active
1/26/2017 23:30	5	1	23	CMT0327	26	ekuprian	1	3	Active
1/26/2017 23:15	5	1	23	CMT0628	26	ekuprian	1	3	Active
1/26/2017 23:30	5	1	23	CMT0628	26	ekuprian	1	3	Active
1/26/2017 23:15	5	1	23	CMT0394	26	epimus	1	3	Active
1/26/2017 23:30	5	1	23	CMT0394	26	epimus	1	3	Active
1/26/2017 23:15	5	1	23	CMT0748	26	alexeyra	1	3	Active
1/26/2017 23:30	5	1	23	CMT0748	26	alexeyra	1	3	Active
1/26/2017 23:30	5	1	23	CMT0625	26	ekuprian	1	3	Active
1/26/2017 23:15	5	1	23	CMT0395	26	Not Detected	1	7	Active
1/26/2017 23:30	5	1	23	CMT0289	26	ekuprian	1	7	Active
1/26/2017 23:15	5	1	23	CMT0441	26	ekuprian	1	7	Active
1/26/2017 23:15	5	1	23	CMT0062G1	26	guol1	2	7	Inactive
1/26/2017 23:30	5	1	23	CMT0062G1	26	guol1	2	7	Inactive
1/26/2017 23:15	5	1	23	CMT0062	26	agvinchh	2	7	Inactive
1/26/2017 23:30	5	1	23	CMT0062	26	agvinchh	2	7	Inactive
1/26/2017 23:15	5	1	23	CMT0625	26	ekuprian	1	7	Active

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col span="2"><col></colgroup>

What I hope to retrieve is dax code that his search base on:
if on the same two rows (cell row & the row that below to here) status appears as "Active" (even for one of them) print "Active" on original cell and leave it blank for lower row & moved on to the next cell (row 3 now).
otherwise take "status" from "Tester name" that not include "G1" text data and below row can be blank.


I hope it more clear now thanks!!