Forum Discussion
Helper IICompare Columns from different Tables in Measure
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
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.
2 Replies
- parry2k
Super User
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 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.
