cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vamshi2020
Helper II
Helper II

Compare 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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Vamshi2020 ,

According to your description, I create this data:

Elifible table:

v-yangliu-msft_0-1611132381844.png

Provider table:

v-yangliu-msft_1-1611132381845.png

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:

v-yangliu-msft_2-1611132381847.jpeg

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.

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @Vamshi2020 ,

According to your description, I create this data:

Elifible table:

v-yangliu-msft_0-1611132381844.png

Provider table:

v-yangliu-msft_1-1611132381845.png

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:

v-yangliu-msft_2-1611132381847.jpeg

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.

View solution in original post

parry2k
Super User III
Super User III

@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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors