Forum Discussion

Macc-PA's avatar
Macc-PA
Helper I
5 years ago
Solved

filtering rows using dates from another table

I am new to powerbi and DAX. I am struggling with how todo the following:   I have a packages table which has a shipped date and a received date for each package. I have a calendar which is used...
Need Help
  • Macc-PA's avatar
    Macc-PA
    5 years ago

    My relationship between transit and dates tables is:

     

    Because there is a relationship between the tables, I had to do the following so that it didnt use the relationship to calculate the measure you'd suggested.

    PackageInTransit2 = 
    CALCULATE(
     IF( MAX(Transit2[Date Shipped] ) <= [SelectedMonth2]
     && MAX(Transit2[Date Recv] ) > [SelectedMonth2]
     /* Then */ , 1
     /* Else */ , IF( MAX(Transit2[Date Recv]) = [SelectedMonth2]
                  /* Then */ , 2
                  /* Else */ , 0 
                  ) 
     ),
     CROSSFILTER ( ZMyCalendar[Date], Transit2[Date Recv], NONE )
    )
     
    SelectedMonth2 =  SELECTEDVALUE( ZMyCalendar[MonthInCalendar] )

     

    The result is as below showing what has been delivered in april and what is outstanding

     

    Thank you Dedmon

     
v-deddai1-msft's avatar
v-deddai1-msft
Community Support
5 years ago

Hi Macc-PA ,

 

Maybe you can try the measure below:

 

 

Measure =
CALCULATE (
    COUNT ( 'packages'[ID] ),
    FILTER (
        ALL ( 'packages' ),
        MONTH ( 'packages'[shipDate] ) < MONTH ( MIN ( CALENDAR[Date] ) )
            && MONTH ( 'packages'[recvDate] ) > MONTH ( MAX ( CALENDAR[Date] ) )
    )
)

 

 

If it doesn't work, please show us some sample data and your expexted output.

 

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

 

Best Regards,

Dedmon Dai

Macc-PA's avatar
Macc-PA
Helper I
5 years ago

Thanks that produces the correct count, however I am having trouble seeing which packages are still in shipment as the filtering on rceived date only shows me the packages that month. I need to see which packages were in the count.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Macc-PA ,

     

    You can create a measure in your table visual filter:

     

     

     Measure = IF( MONTH ( MAX('packages'[shipDate]) ) < MONTH ( MIN ( CALENDAR[Date] ) )
            && MONTH ( MAX('packages'[recvDate]) ) > MONTH ( MAX ( CALENDAR[Date] ) ),1,0)

     

     

    Add it to the table visual and set it is 1:

     

     

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

     

    Best Regards,

    Dedmon Dai

    • Macc-PA's avatar
      Macc-PA
      Helper I
      5 years ago

      The meaure you'd suggested always evaluates to zero regardless of which date I select from the left hand table to filter on and therefore shows all rows in the dataset.

       

       

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        5 years ago

        Hi Macc-PA ,

         

        What is the relationship between calendar table and fact table? Would you please show us sample data (pbix file by onedrive for business)  that we can help you well.

         

        Best Regards,

        Dedmon Dai