Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lseite
Frequent Visitor

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

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):

 

lseite_0-1632477555550.png

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 :

lseite_1-1632477931822.png

same issue with Find formula

 

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

 

many many thanks in advance

 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_0-1632725845999.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_0-1632725845999.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

amitchandak
Super User
Super User

@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> )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.