Problem with joining tables and filters

[aashton]

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?

[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.

[parry2k · Accepted solution]

@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]

Can I ask one more scenario?  What if a job posting does have a Location Identifier, but it does not exist in the Locations table, there is no match?  How can I include these?

[aashton]

Yes, that worked.  thank you so much!!!!

[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 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!

[aashton]

So 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 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 so you want to keep the location identifier which is null regardless of what you filter on location table, correct?

[aashton]

Yes correct

[aashton]

Thank you, but the FTEStatus per job does not always equal 1, it could be .5, .8.  So the total rows does not always equal the total FTEStatus.

[parry2k]

@aashton can you share sample data and how these tables are related and the expected output? 

[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.