Help with Dax - Totals by group

[aashton]

I have a table set-up like:

ID  

Candidate ID

Type  (Application or Contract)

Source   (Ex. Google, web, referral)

Email

I need to count the Total Applications (distinct ID) by Source.  But, if a Candidate ID has an Application and a Contract, I will use the Source on the Contract, but if there is only an Application I will use the Source on the Application.  Unsure how to accomplish this?

[Anonymous · Accepted solution]

Hi  @aashton ,

Here are the steps you can follow：

1. Create calculated column.

Count =
var _column=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])),"Type1",[Type])
return
IF(
    "Application" in _column&&"Contract" in _column,
    COUNTX(
        FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Type]="Contract"),[Source]),
      COUNTX(        FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Type]=EARLIER('Table'[Type])),[Source]))

2. Result:

Best Regards,

Liu Yang

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