how to serach the same value in the more than one row

[Anonymous]

Hello, i have a problem in my sql view i have a situation like this:

Not all the ID_PRATICA have more than one COD_SERVIZIO_AEEG.

I need to create a calculate column where i need to now for every ID_PRATICA whit COD_SERVIZIO_AEEG=PN1 have also a second row whit the COD_SEVIZIO_AEEG=E01

how can i do it? thank you very much.

[m3tr01d · Accepted solution]

Hi,

I think you can do it like this

VAR _CurrentId = MyTable[ID_PRATICA]
VAR _CountForCriteria = 
CALCULATE(
	COUNTROWS( MyTable ),
	MyTable[COD_SERVIZIO_AEEG] IN {"PN1","E01"},
	MyTable[ID_PRACTICA] = _CurrentId
)

RETURN
IF(
	_CountForCriteria >= 2,
	1,
	0
)

First, it stores the Current value of ID_PRATICA  for the current Row you are looking at
Then, It counts how many rows we can find with COD_SERVIZIO_AEEG equals PN1  or E01 for this particular ID_PRATICA.

If its greater thant 2 then it means we found an ID with bother PN1 and E01.

Tell me if it works.

You need to replace MyTable with the name of your table

[amitchandak · Accepted solution]

@Anonymous , Try like

New column =
var _1 = [ID_PRATICA]
var _2 = countx(filter(Table, [ID_PRATICA] =_1, [COD_SERVIZIO_AEEG]="PN1"),[ID_PRATICA])+0
var _3 = countx(filter(Table, [ID_PRATICA] =_1, [COD_SERVIZIO_AEEG]="E01"),[ID_PRATICA])+0
return
if(_2>0 and _3>0 , 1,0)

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can use in operator and summarize function to achieve your requirement:

Search Tag =
VAR summary =
    SUMMARIZE (
        FILTER (
            Table,
            [ID_PRATICA] = EARLIER ( [ID_PRATICA] )
                && [COD_SERVIZIO_AEEG] IN { "E01", "PN1" }
        ),
        [ID_PRATICA],
        [COD_SERVIZIO_AEEG]
    )
RETURN
    IF ( COUNTROWS ( summary ) = 2, "Y", "N" )

Notice:

IN operator allow you to compare with the list of value and summarize can use to remove duplicate records. 

m3tr01d function seems well but it will fail when your table existed duplicate records.(IN operator use 'OR' logic to compare records)

Regards,
Xiaoxin Sheng

[amitchandak · Accepted solution]

@Anonymous , Try like

New column =
var _1 = [ID_PRATICA]
var _2 = countx(filter(Table, [ID_PRATICA] =_1, [COD_SERVIZIO_AEEG]="PN1"),[ID_PRATICA])+0
var _3 = countx(filter(Table, [ID_PRATICA] =_1, [COD_SERVIZIO_AEEG]="E01"),[ID_PRATICA])+0
return
if(_2>0 and _3>0 , 1,0)

[m3tr01d · Accepted solution]

Hi,

I think you can do it like this

VAR _CurrentId = MyTable[ID_PRATICA]
VAR _CountForCriteria = 
CALCULATE(
	COUNTROWS( MyTable ),
	MyTable[COD_SERVIZIO_AEEG] IN {"PN1","E01"},
	MyTable[ID_PRACTICA] = _CurrentId
)

RETURN
IF(
	_CountForCriteria >= 2,
	1,
	0
)

First, it stores the Current value of ID_PRATICA  for the current Row you are looking at
Then, It counts how many rows we can find with COD_SERVIZIO_AEEG equals PN1  or E01 for this particular ID_PRATICA.

If its greater thant 2 then it means we found an ID with bother PN1 and E01.

Tell me if it works.

You need to replace MyTable with the name of your table