Distinct Count of a Measure

[Fuzel]

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.

[TeigeGao · Accepted 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

[Fuzel]

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

[Fuzel]

Hey @TeigeGao  That worked well now. Made a slight mistake initially. Thanks for your response.