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 Folks, I'm fairly new to DAX world. I'm currently running into an issue that I'm not sure how to tackle. I have three different measures that make a distinct count of parts. All 3 measures apply a different set of filters to the same table. I eventually want a measure that distinct counts all the unique parts comparing all three measure.
Below are the three measures that distinct count the parts in the same table.
AMER_PartCount = CALCULATE(DISTINCTCOUNT(TBL_GFE_NETINTAKE_PTC[ITEM]),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[AGE_CLAS]="NPI"),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[SUBREGION]="US-NBD-LEGACY"),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[CATEGORY] = "Net Intake - Purchases Receipts"))
EMEA_PartCount = CALCULATE(DISTINCTCOUNT(TBL_GFE_NETINTAKE_PTC[ITEM]),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[PurAgeClassGrp]="NPI"),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[REGION]="EMEA"))
APJ_PartCount = CALCULATE(DISTINCTCOUNT(TBL_GFE_NETINTAKE_PTC[ITEM]),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[REGION]="APJ"),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[PurAgeClassGrp]="NPI"),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[CATEGORY] ="Net Intake - Non Service Transfer Receipts" || TBL_GFE_NETINTAKE_PTC[CATEGORY] ="Net Intake - Non Service Transfer Receipts (India)" || TBL_GFE_NETINTAKE_PTC[CATEGORY] ="Net Intake - Service Transfer Receipts" || TBL_GFE_NETINTAKE_PTC[CATEGORY] ="Net Intake - Purchases Receipts (India)" || TBL_GFE_NETINTAKE_PTC[CATEGORY] ="Net Intake - Purchases Receipts"))
So these 3 measures give me individually distinct part count but what I want is to be able to distinct count the parts when all these three outputs are combined together. I hope I was clear with my question. Any help is highly appreciated.
Solved! Go to Solution.
Hi @Fuzel ,
In your scenario, we cna use the OR() function to combine these three conditions like below:
Measure = CALCULATE(DISTINCTCOUNT(TBL_GFE_NETINTAKE_PTC[ITEM]),FILTER(TBL_GFE_NETINTAKE_PTC,OR([filter1],[filter2],[filter3]))
Best Regards,
Teige
Hi @Fuzel ,
In your scenario, we cna use the OR() function to combine these three conditions like below:
Measure = CALCULATE(DISTINCTCOUNT(TBL_GFE_NETINTAKE_PTC[ITEM]),FILTER(TBL_GFE_NETINTAKE_PTC,OR([filter1],[filter2],[filter3]))
Best Regards,
Teige
Hey @TeigeGao That worked well now. Made a slight mistake initially. Thanks for your response.
@TeigeGao The solution you provided isn't giving a distinct count of the combined group of all parts.
Not sure what am I still doing wrong
Global_PartCount2 = CALCULATE(DISTINCTCOUNT(TBL_GFE_NETINTAKE_PTC[ITEM]),FILTER(TBL_GFE_NETINTAKE_PTC,(CALCULATE(DISTINCTCOUNT(TBL_GFE_NETINTAKE_PTC[ITEM]),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[AGE_CLAS]="NPI"),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[SUBREGION]="US-NBD-LEGACY"),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[CATEGORY] = "Net Intake - Purchases Receipts")))
||
(CALCULATE(DISTINCTCOUNT(TBL_GFE_NETINTAKE_PTC[ITEM]),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[REGION]="APJ"),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[PurAgeClassGrp]="NPI"),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[CATEGORY] ="Net Intake - Non Service Transfer Receipts" || TBL_GFE_NETINTAKE_PTC[CATEGORY] ="Net Intake - Non Service Transfer Receipts (India)" || TBL_GFE_NETINTAKE_PTC[CATEGORY] ="Net Intake - Service Transfer Receipts" || TBL_GFE_NETINTAKE_PTC[CATEGORY] ="Net Intake - Purchases Receipts (India)" || TBL_GFE_NETINTAKE_PTC[CATEGORY] ="Net Intake - Purchases Receipts")))
||
(CALCULATE(DISTINCTCOUNT(TBL_GFE_NETINTAKE_PTC[ITEM]),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[PurAgeClassGrp]="NPI"),FILTER(TBL_GFE_NETINTAKE_PTC,TBL_GFE_NETINTAKE_PTC[REGION]="EMEA")))))
For example, above measure, global_partcount2 count should be 1 and not 3 for part 036Y0 (2nd part in the list)
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 |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |