cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JosephatNN
Regular Visitor

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.Screenshot (154).pngScreenshot (155).pngScreenshot (156).pngScreenshot (157).png
Any help accorded to me will be of great help. I am also available for a demo call on MS teams. My email is josephatnjoroge@kpmg.co.ke 

2 REPLIES 2
RicoZhou
Community Support
Community Support

Hi @JosephatNN ,

 

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.

My Sample:

RicoZhou_0-1656666518823.png

Date table:

Date =
ADDCOLUMNS (
    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 )
)

Relationship:

RicoZhou_1-1656666561812.png

Create a calculated column to show [Flag].

Flag = IF('Table'[Completion Date]<='Table'[Deadline],1,0)

RicoZhou_2-1656666596377.png

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.

RicoZhou_3-1656666659849.png

RicoZhou_4-1656666666823.png

 

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.

 

Thank you for the solution but still it does not solve the problem

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors