Forum Discussion

megjarvis's avatar
megjarvis
Regular Visitor
3 years ago
Solved

Dynamically select two columns in a table and filter to only show rows where the values differ

(Edited to provide images and link to mock data)

I'm tracking the monthly status (pending / in progress / complete) 57 different projects, and the tracking is expected to continue for 2+ years:

I want to create a visual where the execs I'm reporting to can select a start month and an end month and Power BI will return only those rows where the status in the start month is not the same as the status in the end month. So basically, filter the table to only show the projects whose status changed during the selected months: projects that were started (moved from Pending to In Progress), finished (moved from In Progress to Complete), or started and finished (moved from Pending to Complete).

If the user selects January as the first month and February as the second month:

If the user selects March as the first month and June as the second month:

I know I can create a table and dynamically select the columns I want displayed, but I don't know if it's possible to apply a filter to the rows based on the comparison of the values in the two columns, without creating a calculated column for every column-combination possibility.

Here's a link to a file with with mock data: https://docs.google.com/spreadsheets/d/1U2KS6SkMkywzlbUpUhT_jW58rl33LsL8rKsls0rIK_k/edit?usp=sharing

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi megjarvis ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) We can create measures.

    First_month = var _min_date = MIN('Table'[Date])
var _max_date = MAX('Table'[Date])
var _min_status =MAXX( FILTER( 'Table' , 'Table'[Date]=_min_date) , [Status])
var _max_status = MAXX( FILTER( 'Table' , 'Table'[Date]=_max_date) , [Status])
return
IF(_min_status <> _max_status , _min_status ,BLANK())

    Sencod_month = var _min_date = MIN('Table'[Date])
var _max_date = MAX('Table'[Date])
var _min_status =MAXX( FILTER( 'Table' , 'Table'[Date]=_min_date) , [Status])
var _max_status = MAXX( FILTER( 'Table' , 'Table'[Date]=_max_date) , [Status])
return
IF(_min_status <> _max_status , _max_status ,BLANK())
 
    Change = var _cur_first = [First_month]
var _cur_second = [Sencod_month]
return
SWITCH(TRUE(),[First_month]="Pending"&&[Sencod_month]="In Progress","Started" ,
[First_month]="Pending"&&[Sencod_month]="Complete","Started & Finished" ,
[First_month]="In Progress"&&[Sencod_month]="Complete","Finished" 
)

    (2) Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly. 

3 Replies

    • megjarvis's avatar
      megjarvis
      Regular Visitor
      3 years ago

      Thanks, I've updated my post with images and a link to the mock data.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi megjarvis ,

         

        According to your description, here are my steps you can follow as a solution.

        (1) We can create measures.

        First_month = var _min_date = MIN('Table'[Date])
var _max_date = MAX('Table'[Date])
var _min_status =MAXX( FILTER( 'Table' , 'Table'[Date]=_min_date) , [Status])
var _max_status = MAXX( FILTER( 'Table' , 'Table'[Date]=_max_date) , [Status])
return
IF(_min_status <> _max_status , _min_status ,BLANK())

        Sencod_month = var _min_date = MIN('Table'[Date])
var _max_date = MAX('Table'[Date])
var _min_status =MAXX( FILTER( 'Table' , 'Table'[Date]=_min_date) , [Status])
var _max_status = MAXX( FILTER( 'Table' , 'Table'[Date]=_max_date) , [Status])
return
IF(_min_status <> _max_status , _max_status ,BLANK())
 
        Change = var _cur_first = [First_month]
var _cur_second = [Sencod_month]
return
SWITCH(TRUE(),[First_month]="Pending"&&[Sencod_month]="In Progress","Started" ,
[First_month]="Pending"&&[Sencod_month]="Complete","Started & Finished" ,
[First_month]="In Progress"&&[Sencod_month]="Complete","Finished" 
)

        (2) Then the result is as follows.

        Best Regards,

        Neeko Tang

        If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly.