Calculate and Distinct count with filter (DAX REQ)

[Saxon10]

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.

[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")))

1. 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

[v-yiruan-msft]

Hi @Saxon10 ,

You can create a calculated column or measure as below to get it, please find the details in the attachment.

1. Calculated column

REULT FOR QTY 1 = 
VAR _curitem =
    CALCULATE (
        MAX ( 'DATA'[ITEM] ),
        FILTER ( 'DATA', 'DATA'[ITEM] = 'REPORT'[ITEM] )
    )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( DATA[QTY] ),
        FILTER (
            DATA,
            DATA[COUNT] = 1
                && DATA[ITEM] = 'REPORT'[ITEM]
                && DATA[QTY] <> "0"
        )
    )
VAR _sum =
    SUMX ( FILTER ( 'DATA', 'DATA'[ITEM] = 'REPORT'[ITEM] ), VALUE ( 'DATA'[QTY] ) )
VAR _qty =
    CALCULATE (
        FIRSTNONBLANK ( DATA[QTY], TRUE () ),
        FILTER ( DATA, DATA[ITEM] = 'REPORT'[ITEM] && DATA[QTY] <> "0" )
    )
RETURN
    IF ( _count > 1, "XX", IF ( _sum = 0 && _curitem <> BLANK (), "0", _qty ) )​​

2. Measure

Measure = 
VAR _curitem =
    CALCULATE (
        MAX ( 'DATA'[ITEM] ),
        FILTER ( 'DATA', 'DATA'[ITEM] = SELECTEDVALUE ( 'REPORT'[ITEM] ) )
    )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'DATA'[QTY] ),
        FILTER (
            'DATA',
            'DATA'[ITEM] = SELECTEDVALUE ( 'REPORT'[ITEM] )
                && 'DATA'[COUNT] = 1
                && 'DATA'[QTY] <> "0"
        )
    )
VAR _qty =
    CALCULATE (
        FIRSTNONBLANK ( 'DATA'[QTY], TRUE () ),
        FILTER (
            'DATA',
            'DATA'[ITEM] = SELECTEDVALUE ( 'REPORT'[ITEM] )
                && 'DATA'[QTY] <> "0"
        )
    )
VAR _sum =
    SUMX (
        FILTER ( 'DATA', 'DATA'[ITEM] = SELECTEDVALUE ( 'REPORT'[ITEM] ) ),
        VALUE ( 'DATA'[QTY] )
    )
RETURN
    IF (
        ISBLANK ( _curitem ),
        BLANK (),
        IF ( _count > 1, "XX", IF ( _sum = 0, "0", _qty ) )
    )​​

Best Regards

[Saxon10]

Thanks for your time to understand my requirements. Much appreciated.

Your solution working well and it’s mind blowing.

Could you please kindly help me one more thing, I am trying to add one more criteria here which is 99999 in your exciting solution both calculated column and measure but I got error. It’s exactly the same scenario for 0.

If it possible can you please explain little bit more details how’s it work maybe it will help to understand the Power BI world.

Herewith attached the PBI file and snapshot for your reference. https://www.dropbox.com/s/adkris2hmyq5skp/NEW%20QUERY_Updated.pbix?dl=0

Data and Result (added new scenario) :

 

Snapshot :

[v-yiruan-msft · Accepted solution]

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

[Saxon10]

Hi,

Thank you so much for your time and effort. This is amazing and your solution working well.

I prepared the data manually so I made a mistake for both items. Yes you are right 56902YU should be 1, not XX and 2551 not in my data table.

[Greg_Deckler]

@Saxon10 Just add +0 to the end of your formula?

[Saxon10]

Hi,

Thanks for your reply again.

Can you please advise where can I added +0 in my exciting DAX exactly. 

I am try to apply +0 here but not sure it's right or wrong, it will give correct result for item 7 and 8 meantime it will give wrong result for item 6743.

REULT FOR QTY 1.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")))

Herewith attached the PBI file for your reference. https://www.dropbox.com/s/nic0zr0ohufyaff/NEW%20QUERY.1.pbix?dl=0

[Greg_Deckler]

@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.

[Saxon10]

Herrwith attached the Excel file for your reference.

https://www.dropbox.com/scl/fi/iz4x7yydku3rgu8zkjepn/PBIL.xlsx?dl=0&rlkey=v9vrkhnbzyhgld2dlymyo1f0a

[Saxon10]

Thanks 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 1

Desired 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