Compare Columns from different Tables in Measure

[Vamshi2020]

Hello All,

I have a requirement to take distinct count of Employeeid column with below conditions
EligibleAttributionEndDate>=ProviderEffectiveDate
EligibleAttributionStartDate<=ProviderTerminationDate
EligibleAttributionEndDate>=a
EligibleAttributionStartDate<=b

where a,b are the min and max date values selected in Date slicer and EligibleAttributionEndDate,EligibleAttributionStartDate from Eligible Table and ProviderEffectiveDate,ProviderTerminationDate from provider Table.

Iam able to write last two conditions , but unable to write condition for first two in measure. So, Please help in achieving the requirement. My Dax Measure is below.

Measure=calculate(distinctcount(Eligible[Memberid]),filter(Eligible,EligibleAttributionStartDate<=b),filter(Eligible,EligibleAttributionEndDate>=a))

To the above measure, i need to add first two conditions. So, I need your guidance to achieve the same.

TIA

[v-yangliu-msft · Accepted solution]

Hi  @Vamshi2020 ,

According to your description, I create this data：

Elifible table：

Provider table：

Here are the steps you can follow：

1. Create measure.

Measure =
var _min=MIN('Provider'[ProviderEffectiveDate])
var _max=MAX('Provider'[ProviderTerminationDate ])
return
CALCULATE(COUNT('Elifible'[Memberid]),FILTER('Elifible','Elifible'[EligibleAttributionStartDate]>=_min&&'Elifible'[EligibleAttributionEndDate]<=_max))

2. Result：

You can downloaded PBIX file from here.

If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

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.

[parry2k]

@Vamshi2020 it will be easier to provide the solution if you share sample data Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490