Just wanted you to know that I’m still working your solution and @Mark’s. Just a bit backlogged from a crazy week of work - sorry for the delay.
- Brian
No Problem. It is nice to have feature I guess. Not must to have. But I’m very much interest in learning and getting to know more about DAX.
When you have a chance, can you please send me the Excel data file? I think I’m close to a solution on getting the County level calculation to work, but I need to run Power Query to create the necessary County level disconnected supporting table. (If I try to create the table via DAX, it throws a circular error). I believe this is a granularity mismatch problem - we are currently trying to calculate the dynamic segmentation by County, but from a table (Employer) with a higher granularity, which is why I think we’re getting multiple records per County. I think this will work if we create a separate County table and link it virtually to the Vacancy fact table via TREATAS.
Thanks.
- Brian
Hi @pshetty, we’ve noticed that no response has been received from you since the 24th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
Hi There, I will send you by tomorrow. I got urgent work needs to be finished. Thank you very much for your time.
-Pavi Shetty
OK, I think I’ve got this working right at the county level, and that it was the granularity mismatch problem that I discussed above.
To fix this, I did three things to focus the granularity at the county level:
- created a separate County table in Power Query. However because that table is still coupled with Employer ID, I kept it disconnected from the model since connecting it would result in a many to many relationship
- revised the segmentation calculation to include a TREATAS statement to create the virtual link (without the many to many relationship) between the County table and the fact table
- changed your SWITCH grouping function from a calculated column to a measure to de-link it from Employer (that’s why you were getting the repeated County rows in your visual).
I hope this is helpful. Revised solution file attached.
- Brian
eDNA Forum - VacancyTest (3) solution.pbix (270.9 KB)
Okay. Thank you very much. I’m going to try this and let you know. I’m on critical task and took time to reply you. I apologize.
Hi @pshetty, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!