Measure to check if field value exists in another column (same table)

[lseite]

Hello all, 

i am using power BI with connexion to a SQL server. Hence, i cannot create new tables or new columns. This limits the possibilities...

I am trying to evaluate if value from Table [Facture] Column [numfacture] exists, whatever the row, in Table [Facture] Column [numavoir]

Example of data (only really short part of total data):

here, i would want a measure with 1 or yes if D117002940 exists in [numavoir] column, else 0. 

The data beginning with D represents quotations, and data beginning with F are invoices.

Once a D quotation has been invoiced, an F invoice is created, linked to the D quotation. If it's not invoiced (customer refusal for example), no F invoice is created. 

What i want to show are the D quotations which haven't been linked to any F invoices. In English, i'm trying to show our "non conversion" rate. I would also need to remove date filter on F invoice, as a quotation can be created in August an only invoiced in September for example.

i've been trying to use lookupvalue, but i'm blocked :

same issue with Find formula

if someone can think of a measure that would work for this...

many many thanks in advance

[v-yiruan-msft · Accepted solution]

Hi @lseite ,

It seems that you are connecting to SQL Server using Direct Query connection mode, so you can create a measure as below, please find the details in the attachment.

Measure = 
VAR _curnumfact =
    SELECTEDVALUE ( 'Facture'[numfacture] )
VAR _getnumav =
    CALCULATE (
        MAX ( 'Facture'[numavoir] ),
        FILTER ( ALLSELECTED ( 'Facture' ), 'Facture'[numavoir] = _curnumfact )
    )
RETURN
    IF ( ISBLANK ( _getnumav ), 0, 1 )

Best Regards

[onlywynter]

Is there a way to get the number of times numfacture occurs in numavoir?

[amitchandak]

@lseite , I am assuming you are using a direct query connection. In that case, only simple columns are allowed.

Or you have to use import mode or do this calculation at DB level

In case of measure

do this calc inside some X function

example Countx(Table, <Your calc> )