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
gauravnarchal
Post Prodigy
Post Prodigy

Need help with measure

Hello

 

I need your help in creating the below measures. I have also attached the sample data in the case that helps. Click Here

 

I want to search all data of Table “Report” with table “ARAMT” & table “Invoice Number” to get the gross amount and Invoice number starting with "9".

 

  • IF TABLE REPORT[TRNC] = “RFND” & TABLEREPORT [SERIALNUMBER] = TABLE ARAMT [SERIALNUMBER]  RETURN INVOICENUMBER STARTING WITH “9 “ FROM THE INVOICE NUMBER TABLE

 

  • IF TABLE REPORT[TRNC] = “RFND” & TABLEREPORT [SERIALNUMBER] = TABLE ARAMT[SERIALNUMBER]  RETURN TABLE AMT[GROSSAMOUNT] OF THE TABLE INVOICENUMBER[INVOICENUMBER] STARTING WITH “9“

 

else - any value is not found in the table “ARAMT” & “Invoice Number” it should show “Unmatched”.

 

Thank you in advance.

 

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @gauravnarchal ,

 

In this case you need to create a table with the serial number and make a relationship between the ARAMT and then create the following measure:

 

 

Total =
VAR InvoiceNumberFirst =
    LEFT ( SELECTEDVALUE ( 'Invoice Number'[InvoiceNumber] ), 1 )
RETURN
    IF (
        InvoiceNumberFirst = "9",
        CALCULATE (
            SUM ( ARAmt[GrossAmount] ),
            CROSSFILTER ( Report[SerialNumber], 'Table'[SerialNumber], BOTH )
        ),
        "Unmatech"
    )

 

The filtering of the table can be added to the filtering of the visualization.

 

I did no make the full filtering since I'm not understading if you want to have the TRNC for all invoices or if ones gives the totals and the others should be unmatched.

 

See result attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

Hi @gauravnarchal ,

 

In this case you need to create a table with the serial number and make a relationship between the ARAMT and then create the following measure:

 

 

Total =
VAR InvoiceNumberFirst =
    LEFT ( SELECTEDVALUE ( 'Invoice Number'[InvoiceNumber] ), 1 )
RETURN
    IF (
        InvoiceNumberFirst = "9",
        CALCULATE (
            SUM ( ARAmt[GrossAmount] ),
            CROSSFILTER ( Report[SerialNumber], 'Table'[SerialNumber], BOTH )
        ),
        "Unmatech"
    )

 

The filtering of the table can be added to the filtering of the visualization.

 

I did no make the full filtering since I'm not understading if you want to have the TRNC for all invoices or if ones gives the totals and the others should be unmatched.

 

See result attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.