I have a two tables data and report.
Data:
In data table contain item, Qty and Count, in this table the qty column stored always as a text and count column as a number.
There is lot of duplicated row in this table according to the count.
Report:
In Report table the item column is unique.
The item column are common in between two tables.
Result:
I would like to bring the qty from data table into report table according to the item and count=1 only. (Not 0)
Scenario
- The same item contain multiple qty (100, 200, 350 ,0) according to the same count number 1, in this scenario the expected result is “XX”. (Please refer in data table the following items- 123456, 567, 116)
- The same item contain two different qty which is number and 0 (100 and 0) according to the same count number 1, in this scenario the expected result is number (ignore the 0 here). (Please refer in data table the following items- 67543)
- If item contain 0 only in data table then return the same thing in report table according to the item and count number1. (Please refer in data table the following item- 7,8)
- If item not available in data table then return blanks in report table according to the item and count number1. (Please refer in data table the following item – 444, 10 ,12)
I am applying the following New calculated column (DAX) in report table REULT FOR QTY = IF(CALCULATE(DISTINCTCOUNT(DATA[QTY]),FILTER(DATA,DATA[COUNT]=1),FILTER(DATA,DATA[ITEM]='REPORT'[ITEM]))>1,"XX",CALCULATE(FIRSTNONBLANK(DATA[QTY],1),FILTER(DATA,DATA[ITEM]='REPORT'[ITEM])))
It's almost working fine expect the scenario No 2. (The same item contain two different qty which is number and 0 (100 and 0) according to the same count number 1, in this scenario the expected result is number (ignore the 0 here). (Please refer in data table the following items- 67543)
I am trying to ignore the 0 were same item contain 0 and same number in my exciting DAX.
Any advice please.
Here is the power bi file for your reference. https://www.dropbox.com/s/810ex5g0b06ubb6/NEW%20QUERY.pbix?dl=0
DATA TABLE:
Aucun commentaire:
Enregistrer un commentaire