Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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".
else - any value is not found in the table “ARAMT” & “Invoice Number” it should show “Unmatched”.
Thank you in advance.
Solved! Go to Solution.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |