title: DISCOUNT with multiple criteria in Power BI
body: <p>I have two tables are Data and Report.</p>
  <p>Data Table: In the Data table, two columns are Item, Qty, and Order. The Item columns contain as a text &amp; number and qty and number column stored as text and number.</p>
  <p>The item column is repeated according to the order and the same item column contains two different qty according to the order column.</p>
  <p>Report Table:</p>
  <p>I have a unique item column.</p>
  <p>Data and Report file looks like.</p>
  <p>Data</p>
  <p>ITEM    QTY ORDER
  123 200 1
  123 210 0
  5678    220 1
  5678    230 0
  5555    240 1
  6666    250 1
  9876    260 1
  2345    270 1
  901 280 1
  901 280 1
  902 300 1
  902 300 1
  123456  200 1
  123456  200 1
  123456  210 1
  123456  210 1
  123456  0   1
  567 200 1
  567 210 1
  567 210 1
  567 0   1
  453 5000    1
  453 5000    1
  453 5000    1
  453 5000    1
  112 5000    1
  112 5000    1
  112 5000    1
  112 5000    1
  116 5000    1
  116 5001    1
  116 0   1
  116 0   1
  116 5000    0
  116 5001    0
  116 0   0
  116 0   0</p>
  <p>Report</p>
  <p>ITEM    DESIRED RESULT (QTY)
  123 200
  5678    220
  5555    240
  6666    250
  9876    260
  2345    270
  901 280
  902 300
  123456  MIXED
  567 MIXED
  4444    NA
  12  NA
  10  NA
  453 5000
  112 5000
  116 MIXED
  Expand snippet
  Desired Result</p>
  <p>I would like to pull the qty against the order “1” from the data table into the report table according to the item.</p>
  <p>If the item is found in the data table then return the qty in the report table according to the item. {Please refer to the “Data” and “Report table for item 123 and 5678 etc….}</p>
  <p>If an item is not found in the data table then return “NA” in the report table according to the item. {Please refer to the “Data” and “Report table for item 10, 12,444}</p>
  <p>The same item contains two different qty then returns as a text “Mixed” in the report table according to the item. {Please refer to the “Data” and “Report table for item 123456,116 &amp; 567}</p>
  <p>Currently I am using the following calculated column CURRENT DAX FOR QTY = LOOKUPVALUE(DATA[QTY],DATA[ITEM],'DESIRED RESULT'[ITEM],DATA[ORDER],1,&quot;NA&quot;) enter image description here</p>
  <p>It’s almost working fine but it’s giving the wrong result “NA” were two different qty for the same item &amp; two different order (0,1) or (1) or (o) {Please refer to the “Data” and “Report table for item 123456, 116 &amp; 567} but the desired result is “Mixed” those three items.</p>
  <p>Note: I convert the qty column from number to text otherwise it gives an error, is there any alternative option to achieve my result.</p>
  <p>Herewith attached the PBI file for your reference <a href="https://www.dropbox.com/s/hf40q27pvn3ij2g/DAX-LOOKUPVALUE%20FILTER%20BY.pbix?dl=0" rel="nofollow noreferrer">https://www.dropbox.com/s/hf40q27pvn3ij2g/DAX-LOOKUPVALUE%20FILTER%20BY.pbix?dl=0</a>.</p>