Temporary Virtual Table based on Slicer

[Skykingcljr]

Have Table1 that includes columns: EmplId, ReportedDate, TimeReported, ProjectId, Activity. Table2 that includes columns: EmplId, ProjectId, AssignmentStartDate, AssignmentEndDate. Table3 has columns: ProjectId, ProjectName, Customer. Table1 and Table2 both relate many-to-one to Table3 on ProjectId. 

Table1 includes records where ProjectId is blank (i.e., where the Activity value is not project related).

Have slicer based on Table3[Customer]. Another slicer based on Table1[ReportedDate], configured as between.

Challenge: Need to return all records on Table1 that have ReportedDate in selected timeframe for each EmplId that exists on Table2 where the following is true...

- ProjectId aligns to selected Table3[Customer] 

- AssignmentStartDate and AssignmentEndDate encompass some portion of the selected ReportedDate range

Desired results will include all Table1 records for those in-scope EmplIds, whether aligned to the selected Customer or not.

Has anyone solved for this scenario?

Thanks much for any guidance you can share.

[v-nmadadi-msft]

Hi @Skykingcljr 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @Skykingcljr 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Shahid12523]

Use slicers for Customer (from Table3) and ReportedDate (from Table1).
Identify EmplIds from Table2 where:
Their ProjectId maps to the selected Customer.
Their assignment dates overlap with the selected ReportedDate range.
Return all Table1 records for those EmplIds, even if the record’s ProjectId is blank or unrelated.

[danextian · Accepted solution]

Hi @Skykingcljr 

The attached pbix is maybe what you're looking for... or not. 

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[wardy912]

Hi @Skykingcljr 

Add the following measure to table1

IsInScopeEmployee = 
VAR SelectedCustomer = SELECTEDVALUE(Table3[Customer])
VAR MinDate = MIN(Table1[ReportedDate])
VAR MaxDate = MAX(Table1[ReportedDate])

RETURN
CALCULATE(
    COUNTROWS(Table2),
    FILTER(
        Table2,
        Table2[AssignmentStartDate] <= MaxDate &&
        Table2[AssignmentEndDate] >= MinDate &&
        RELATED(Table3[Customer]) = SelectedCustomer &&
        Table2[EmplId] = MAX(Table1[EmplId])
    )
)

Now, add the following filter to the table1 visual
IsInScopeEmployee = 0

You will get the following result, showing all times including those not related to a customer

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[TomMartens]

Hey @Skykingcljr ,

consider a pbix that contains sample data but still reflects your semantic model, meaning (relationships, calculated columns, and measures). Upload the file to OneDrive, Google Drive, or Dropbox, and share the link (make sure that the file can be accessed with out logging in).

Do not forget to explain the expected outcome based on the data you provided.

If you are using Excel to create the sample data, share the Excel file as well.

Regards,

Tom