Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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:
Hi @matteog ,
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)
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 |
@matteog ,
Can you pls let me know the output you need.
Not very clear from the above example.
Regards,
HN
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |