Forum Discussion
Helper VProblem with joining tables and filters
Hello,
I have a job posting table and a location table, linked on Location ID. I have a couple measures, one on Job Posting table, total Postings, and one on the Location table, total Staffing, and use Total Postings/Total Staffing to find another measure. I need to filter out locations that are less than a year old. If I create the column on the Location table to find new locations, and use this to filter, it filters the Locations table (thus reducing the Total Staffing measure), but it also removes all Job Postings that do no link to the Locations table. I want to keep all Job Postings that do not link to the Locations table in the Total Postings count. So I then created a left join on the Location table and added the New/Existing flag into the Job Postings table. But now when I fitler on this, it reduces the Total Postings but does not filter the Total Staffing on the Locations table.
Any idea how I can filter out new locations, but keeps all job postings that do not link(exist) in the Locations table?
aashton well then make small tweak and you will be good to go:
Total FTEStatus = SUM(JobRequisitions[FTEStatus]) + IF ( ISFILTERED ( 'CRM Sharepoint'[New/Existing Column] ), CALCULATE (sum(JobRequisitions[FTEStatus]), REMOVEFILTERS ('CRM Sharepoint'), KEEPFILTERS (JobRequisitions[Location_Identifier] = "NULL" ) ) , 0 )
14 Replies
- parry2k
Super User
aashton replace countrows with whatever summarization you want to use, sum ( Table[Column Name] )
The core logic is going to be the same.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
aashtonSo I've changed it to this:
Total FTEStatus = SUM(JobRequisitions[FTEStatus]) + CALCULATE (sum(JobRequisitions[FTEStatus]), REMOVEFILTERS ('CRM Sharepoint'), KEEPFILTERS (JobRequisitions[Location_Identifier] = "NULL" ) )I think it's doing something with rounding, because the sum is not coming out correct even though I am getting the correct records. I think it's making the .95, .9, .8 etc as 1.
- parry2k
aashton well then make small tweak and you will be good to go:
Total FTEStatus = SUM(JobRequisitions[FTEStatus]) + IF ( ISFILTERED ( 'CRM Sharepoint'[New/Existing Column] ), CALCULATE (sum(JobRequisitions[FTEStatus]), REMOVEFILTERS ('CRM Sharepoint'), KEEPFILTERS (JobRequisitions[Location_Identifier] = "NULL" ) ) , 0 ) - aashton
Job Posting table:
Location_Identifier RTR FTEStatus NULL JR3391 1 NULL JR3394 1 NULL JR3395 1 NULL JR3396 1 NULL JR3397 1 NULL JR3398 1 NULL JR3399 1 NULL JR3413 1 NULL JR3414 1 NULL JR3415 1 NULL JR3416 1 NULL JR3418 1 AA 18597 1 AA JR1696 1 CT1102 6216 1 CT1102 7393 1 CT1102 7394 1 CT1102 JR1772 1 CT1102 JR3478 1 CT1138 JR2009 1 Total FTEStatus 20 Location Table
Location_Identifier Total Staffing Model New/Existing CT1102 20 Existing CT1138 50 New Total Staffing Model 70 If I want to filter on New/Existing, and only include Existing, I will remove CT1138 and the 50 from the Total Staffing Model, and the Job Posting for CT1138 of 1 for a total of 19 FTEStatus. But when I filter on only existing, it also removes those 12 that do not exist in the Location table. I do not want to remove these 12 from the total FTEStatus.
- parry2k
aashton here is the measure which will do it:
Job Posting Count = COUNTROWS ( JobPosting ) + CALCULATE ( COUNTROWS ( JobPosting ), REMOVEFILTERS ( Location ), KEEPFILTERS ( JobPosting[Location_Identifier] = "NULL" ) )✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
- parry2k
aashton make sure measure format is set to decimal, select the measure and check the format, and if required, change it to decimal
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
- aashton
Yes, thank you so much that works. But now I'm thinking, if I let the users change the filters, they can change from existing to new, and it will still work. But if they remove the filter and want to see all of them, the total FTEStatus will be too high....because it will add the total FTEStatus, but then add the ones missing the Location Identifiers in again....
- parry2k
aashton the most logical sense is to fill the missing location id's in the location table rather than juggling with DAX, again you can always write a complex DAX or you make your data ready that is ready to consume and have less juggling the DAX. I would recommend 2nd option in this case, although DAX can achieve what you are looking for, it is overkill.
This is how I will approach it.