Dax formula summarize?

[Vanessa250919]

Hello,

I created one formula dax:

Count Distinct Agent total (VLT>0) = CALCULATE( DISTINCTCOUNT(MASTERTABLE[FULL_EMPLID]),MASTERTABLE[WLT_PO]>0,DIM_EMPLOYEE[HR_STATUS] = "ACTIVE")

But I have one issue, I need to calculate the nbrs of agent ACTIVE with WLT_PO >0 but WLT_PO By agent and not total WLT_PO, it's possible?

[Anonymous · Accepted solution]

Hi @Vanessa250919 ,

You could use ALLEXCEPT() function.

https://docs.microsoft.com/en-us/dax/allexcept-function-dax 

Count Distinct Agent total (VLT>0) = CALCULATE( DISTINCTCOUNT(MASTERTABLE[FULL_EMPLID]),FILTER(ALLEXCEPT(MASTERTABLE,[agent] ),MASTERTABLE[WLT_PO]>0,DIM_EMPLOYEE[HR_STATUS] = "ACTIVE"))

Best Regards,

Jay

[amitchandak]

@Vanessa250919 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try like

Count Distinct Agent total (VLT>0) = CALCULATE( DISTINCTCOUNT(MASTERTABLE[FULL_EMPLID]),Filter(MASTERTABLE,MASTERTABLE[WLT_PO]>0),Filter(DIM_EMPLOYEE, DIM_EMPLOYEE[HR_STATUS] = "ACTIVE") )