Forum Discussion

gauravnarchal's avatar
gauravnarchal
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

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.

 

 

Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    5 years ago

    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.

1 Reply

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    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.