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.

[TABLE="width: 599"]
<tbody>[TR]
[TD]MTDate[/TD]
[TD]Weekday[/TD]
[TD]Non PrimeTime[/TD]
[TD]Non Prime Time hour[/TD]
[TD]TesterName[/TD]
[TD]Day[/TD]
[TD]Login[/TD]
[TD]TesterStatus[/TD]
[TD]TesterState[/TD]
[TD]status[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0748G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0748G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0289G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0289G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0395G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0395G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0441G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0441G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0628G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0628G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0625G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0625G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0327G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0327G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD]Logged Out[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0291G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD]Down[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0291G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD]Down[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0534[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Inactive[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0534[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Inactive[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0534G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Inactive[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0534G1[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Inactive[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0289[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0441[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0291[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0291[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0395[/TD]
[TD="align: right"]26[/TD]
[TD]Not Detected[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0327[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0327[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0628[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0628[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0394[/TD]
[TD="align: right"]26[/TD]
[TD]epimus[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0394[/TD]
[TD="align: right"]26[/TD]
[TD]epimus[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0748[/TD]
[TD="align: right"]26[/TD]
[TD]alexeyra[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0748[/TD]
[TD="align: right"]26[/TD]
[TD]alexeyra[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0625[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0395[/TD]
[TD="align: right"]26[/TD]
[TD]Not Detected[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0289[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0441[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0062G1[/TD]
[TD="align: right"]26[/TD]
[TD]guol1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD]Inactive[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0062G1[/TD]
[TD="align: right"]26[/TD]
[TD]guol1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD]Inactive[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0062[/TD]
[TD="align: right"]26[/TD]
[TD]agvinchh[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD]Inactive[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0062[/TD]
[TD="align: right"]26[/TD]
[TD]agvinchh[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD]Inactive[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017 23:15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]CMT0625[/TD]
[TD="align: right"]26[/TD]
[TD]ekuprian[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD]Active[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col span="2"><col></colgroup>[/TABLE]




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!!