Filter one table column to get the results of the other
Hi Team, I am new to the community and would really appreciate your help.
I am trying to create a table in the Power BI desktop report to show the users who did not submit the MS Form on time, based on the completion date(the date that the form was filled in). The report should flag users who have not yet filled the form for the reporting week. The reporting week should be dynamic (a report user can filter any week and be able to see guys who did not fill in the form within that week. Any help accorded to me will be of great help. I am also available for a demo call on MS teams. My email is email@example.com
As far as I know, Power BI doesn't support us to create a table with dynamic result based on filter or slicer. Here I suggest you to create a visual which is filtered by [Flag] column. I think there should be two columns in your report data table, [Completion Date] and [Deadline]. We need to compare [Completion Date] and [Deadline] to determind whether the report is submited on time.
CALENDAR ( MIN ( 'Table'[Week Start Date] ), MAX ( 'Table'[Week End Date] ) ),
"Week Start Date",
[Date] - WEEKDAY ( [Date], 2 ) + 1,
"Week End Date",
[Date] + 7
- WEEKDAY ( [Date], 2 )
Create a calculated column to show [Flag].
Flag = IF('Table'[Completion Date]<='Table'[Deadline],1,0)
Create a table visual by [User Name], add [Flag] into visual level filter and set it to show items when value =1.
Result is as below.
Best Regards, Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.