Forum Discussion
Post ProdigyCalculate and Distinct count with filter (DAX REQ)
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 and Desired Result.
Hi Saxon10 ,
I updated your sample pbix file again, please check the attachment for the details. By the way, I got the different results about item 2551 and 56902YU...Could you please confirm whether their DESIRED RESULT (QTY) are correct? There is no data about item 2551 in DATA table. I think the final qty should be blank. For item 56902YU, it should be 1. Could you please provide the related logic? Thank you.
Best Regards
11 Replies
- Greg_Deckler
Community Champion
Saxon10 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Saxon10Thanks for your reply and sorry for the inconvenience.
1. Here is the table for your reference.
The item column are common in between two tables.
I would like to get the qty from data table into report table according to the item and count.
Data
ITEM QTY COUNT
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
567 200 1
567 210 1
567 210 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 5000 0
116 5001 0
200YU 100 1
56902YU 99999 1
56902YU 99999 1
56902YU 99999 1
56902YU 1 1
56902YU 1 1
90 99999 1
91 99999 1
91 99999 1
90 99999 0
90 99999 0
7 0 1
7 0 1
8 0 1
67543 0 1
67543 0 1
67543 99 1
67543 99 1Desired Result
ITEM DESIRED RESULT (QTY)
123 200
5678 220
5555 240
6666 250
9876 260
2345 270
901 280
902 300
123456 XX
567 XX
4444
12
10
453 5000
112 5000
116 XX
200YU 100
56902YU XX
90 99999
91 99999
7 0
8 0
67543 99- Saxon10
Herrwith attached the Excel file for your reference.
https://www.dropbox.com/scl/fi/iz4x7yydku3rgu8zkjepn/PBIL.xlsx?dl=0&rlkey=v9vrkhnbzyhgld2dlymyo1f0a
- Greg_Deckler
Saxon10 Check out the PBIX attached below and let me know if it is correct or not. Page 2
- Saxon10
Thanks for your replay again. My desired result is different.
Please refer the below mentioned snapshot of my desired result look like and DAX result as well. Also you can see the difference in-between Desired result and DAX result. The below mentioned DAX almost working fine but it will fail were the qty is 0. (Please refer the item – 7 and 8 for both tables)
REULT FOR QTY 1 = IF(CALCULATE(DISTINCTCOUNT(DATA[QTY]),FILTER(DATA,DATA[COUNT]=1),FILTER(DATA,DATA[ITEM]='REPORT'[ITEM]),FILTER(DATA,DATA[QTY]<>"0"))>1,"XX",CALCULATE(FIRSTNONBLANK(DATA[QTY],TRUE()),FILTER(DATA,DATA[ITEM]='REPORT'[ITEM]),FILTER(DATA,DATA[QTY]<>"0")))
- If same item does contain multiple qty according to the count in data table then return “XX” in report table according to the item. (in this scenario the Qty column <>0 only need to be considered)
2.If same item does not contain multiple qty according to the count in data table then return the same thing in report table according to the item. (in this scenario the Qty column <>0 only need to be considered)
3. If same item does contain only 0 in data table then return the same thing in report table according to the item.
4.If item can’t found in data table then return “Blanks”
Note: In both table the common column is item and the filter criterial is count column =1 only in order to pull the qty from data table into report table.
Herewith attached the PBI file for more information.
https://www.dropbox.com/s/dtewq6x0lqr7nw0/NEW%20QUERY.pbix?dl=0
- Greg_Deckler
Saxon10 Just add +0 to the end of your formula?
