count in related tables

[matteog]

Hello,

I've 2 related tables (TABLE 1 related to TABLE 2 through product field) and the following situation:

TABLE 1

REFERENCE	STATUS	AMOUNT	DATE
reference1	status 1	1	01/03/2020
reference1	status 2	0	01/03/2020
reference1	status 1	1	01/03/2020
reference2	status 1	0	01/03/2020
reference2	status 2	1	01/03/2020
reference2	status 3	0	01/03/2020

TABLE 2

REFERENCE	PRODUCT
reference1	producta
reference2	productb

I'd need a custom measure doing the following calculation: count (distinct) the number of PRODUCT(s) which have REFERENCE(s) in status 1 with amount > 0. In my sample case I'd like to have only 1 as result since:

- producta has 2 REFERENCE(s) in status 1 >0 but I want it counted once

- productb has 0 REFERENCE(s) in status 1 > 0

Do you have any suggestions?

thanks

Best Regards

[v-zhenbw-msft · Accepted solution]

Hi @matteog ,

We can use the following steps to meet your requirement.

1. Create a relationship between table 1 and table 2.

2. Then we can create a measure like this,

Refer = 
var max_R = MAX('Table 1'[REFERENCE])
return
CALCULATE(DISTINCTCOUNT('Table 2'[PRODUCT]), FILTER('Table 1', 'Table 1'[STATUS] = "status 1" && 'Table 1'[AMOUNT] > 0 && 'Table 1'[REFERENCE]=max_R))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.       

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[matteog]

hi @v-zhenbw-msft ,

sorry for the delay, but I had to upgrade my version to open your pbix.

The solution works well and it achieves my goal. However, it is not clear to me why to use a variable. The measure works fine even without it. is it correct?

While I was waiting for an answer I tried by myself and tried this custom measure:

Measure = calculate(distinctcount('Table 2'[PRODUCT]); 'Table 1'[AMOUNT]>0; CROSSFILTER('Table 2'[REFERENCE];'Table 1'[REFERENCE];Both))

[harshnathani]

Hi @matteog ,

Ref = CALCULATE(DISTINCTCOUNT(Table18[REFERENCE]), (FILTER(Table18, Table18[STATUS] = "status 1" && Table18[AMOUNT] > 0)))

Replace Table18 with Table1.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[matteog]

Hi @harshnathani 

thank you very much for your support. Unfortunately I faced a slightly more difficult situation so that the solution does not fit .

I changed my example as below. In that case, I'd like to have always 1 as result (because I wanted to have counted PRODUCT(a) only once even if has 2 references in satus 1 and with amunt > 0 

Can you help me?

thanks and sorry for the reiteration.

TABLE 1

REFERENCE	STATUS	AMOUNT	DATE
reference1	status 1	1	01/03/2020
reference1	status 2	0	01/03/2020
reference1	status 1	1	01/03/2020
reference2	status 1	1	01/03/2020
reference2	status 2	1	01/03/2020
reference2	status 3	0	01/03/2020

TABLE 2

REFERENCE	PRODUCT
reference1	producta
reference2	producta

[harshnathani]

@matteog ,

Can you pls let me know the output you need.

Not very clear from the above example.

Regards,

HN

[matteog]

Hi @harshnathani ,

according to the example, I'd like to have a custom measure counting the  product (not the reference) if conditions are met  (status = 1 and amount > 0). The point with distinct count in table 1 is that if a product has 2 references (associated to a unique product) with conditions met, the measure counts 2 references. But I do not want that. I want  only to have the product "a" counted one time (I don't know if a possible solution could be to have a lookup of the product in table 1 and then make a distinct count of the field product with conditions). 

Let me know if it not clear and you need further clarifications,

thanks

[harshnathani]

@matteog 

Regards,

Harsh Nathani