Dynamically update the values based on slicers in the Date Table

[Anonymous]

Hello PowerBI Community, 

I am hoping to get some help with my situation. I have a Table called Leads. I have created a Date table. Both these tables have relationship based on Year and Month. Now I have two slicers in the Lead table called State and Center. When I filter values from State and Center, it should automatically update the count of Unqualified Leads in the Date table (Destination) based on the slicers selected in the Lead table (Source)

Below are the screenshots:

The below formula gives me correct count at the Company level, but when I try to filter it at individual states/Centers the count does not change.

Unqualified Leads = CALCULATE(COUNTROWS('lead'), ALLSELECTED('lead'[State], 'lead'[Center]), FILTER('lead','lead'[YYYYMM] = 'Date'[YYYYMM]))

[Anonymous]

Hello, 

 I have attached the sample data in excel format. I have two objects that are Leads and Enrollment that are not related to each other. I am trying to create a rate calculation. I take all the leads for a month divide it by enrollments for that month and get a value. I use date  table for this, but I do not have dimensions like Center State etc. So I am able to only get the overall date and not at Center State Level. Any help to design a data model within Power BI is greatly appreciated.

[Ashish_Mathur]

Hi,

Share some data in a format that can be pasted in an MS Excel file and show the expected result.

[Anonymous]

Hello Ashish, 

How do I attach the excel file. I do not get an option to do that.

[Ashish_Mathur]

Hi,

Upload the file to Google Drive or similar service and share the download link here.

[Anonymous]

Hello Ashish, 

Please find the link to sample data. Let me know if you are able to access it. 

Object# 1  Enrollment

Object #2  Leads

Both objects do not have a relationship. 

Metric - Leads for current month/Enrollment for current month (expressed as %)

I also need to filter the metric by Center, State and Referral Category. If I apply the filter, the rate should change dynamically.

https://docs.google.com/spreadsheets/d/1L1nbAcwKcZdgfKmttADfPlTLL5F25kUz/edit?usp=sharing&ouid=10235...

[Ashish_Mathur]

Hi,

Please find attached my PBI file.

Hope this helps.

[Anonymous]

Thank you Ashish. I see it is working for Referral Source Category, but I also need it by Center and State. So I suppose I will need to create separate tables (one for Center and another one for State) and then establish relationships?

Question - I have tried this in my current PowerBI model and it will not let me have multiple relationships. Is there s specific way you would suggest to establish that relationship?

[Ashish_Mathur]

Yes, relationships need to be created.  Ensure they are Many to One and Single.

[Anonymous]

Thank you Ashish! How would you create lead lag calculations? When I create them in the date table I cannot filter them out.

[Ashish_Mathur]

You are welcome.  Not clear with your question.  Share some data and show the expected result. 

[Anonymous]

Hi @Anonymous ,

Please try to create a measure.

Unqualified Leads Measure = 
CALCULATE(
    COUNTROWS('lead'),
    ALLSELECTED('lead'[State], 'lead'[Center]),
    FILTER('lead', 'lead'[YYYYMM] = MAX('Date'[YYYYMM]))
)

or

Unqualified Leads Measure = 
CALCULATE(
    COUNTROWS('lead'),
    FILTER( ALLSELECTED('lead'), 'lead'[YYYYMM] = MAX('Date'[YYYYMM]))
)

This measure will aggregate the count of rows in the 'lead' table based on the selected 'lead'[State] and 'lead'[Center] while filtering the 'lead'[YYYYMM] to match the maximum 'Date'[YYYYMM] in the current context.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Anonymous]

Hello, 

No this did not help solve my problem.