non trivial DISTINCTCOUNT with multiple filters

[Anonymous]

Dear members, 

i've got some headaches with calculation which is looks easy to calculate by eyes and impossible for me to get correct result in PBI. 

Goal: to get correct claims statistics and their visualization

Sample data are located here https://dropmefiles.com/AdxlX 

Data description:

• Presented data file contains dates of claims receving, order number, material code (SKU), investigation result and column which is explain is order taken into KPI calculation or not.
•  investigation result is checked and resulted on SKU level: for instance, recevied claims for the order 53901 contains 6 SKUs which are confirmed or not

•  if order number is 0 that means mentioned SKUs are belong to order number which is the latest before 0: row 234 is belong to row 233 as of Order number

Rules for KPI calculation: 

• KPI is calculated based on taken (column Claims taken for KPI calculation or not) and order qty (meaning: if some any of SKUs in claimed order is confirmed  then whole order is confirmed and should be calculated as  confirmed claim) per period (year, month, day)

Obstacles/headaches:

1. how to correctly calculate it? 

using:

Nr of confirmed orders = CALCULATE(DISTINCTCOUNT('Sheet1'[ORDER number]);FILTER('Sheet1';'Sheet1'[ORDER number]>0&&'Sheet1'[Claim is taken for KPI calculation or not]="taken"&&'Sheet1'[Investigation result]="confirmed"))

Nr of NOT confirmed orders = CALCULATE(DISTINCTCOUNT('Sheet1'[ORDER number]);FILTER('Sheet1';'Sheet1'[ORDER number]>0&&'Sheet1'[Claim is taken for KPI calculation or not]="taken"&&'Sheet1'[Investigation result]="not confirmed"))

i have got oucome that the same order is calculated as confirmed and not confirmed due to some of its SKUs are confirmed and not confirmed (as an example above, order 53901 )

1. Using Prompt page i'd like to return order numbers for the visualized table, but it shows all received claims and not only conformed or not confirmed - please advise, how to fix and where i'm wrong.

is it possible somehow to calculate correct results for 2 questions?

thank you in advance.

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

Do you mean to show "Prompt page" based on which measure you select? Tooltip will show values based on the context of current row, so I think it is impossible to do it in one table. You could create two tables with two measures in tooltip page.

Yes =
CONCATENATEX (
    CALCULATETABLE (
        DISTINCT ( Sheet1[ORDER number] ),
        FILTER ( Sheet1, 'Sheet1'[Investigation result] = "confirmed" )
    ),
    'Sheet1'[ORDER number],
    ", "
)

Not =
CONCATENATEX (
    CALCULATETABLE (
        DISTINCT ( Sheet1[ORDER number] ),
        FILTER ( Sheet1, 'Sheet1'[Investigation result] = "not confirmed" )
    ),
    'Sheet1'[ORDER number],
    ", "
)

Here is the result.