Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

parry2k
Super User
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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors