Forum Discussion

Macc-PA's avatar
Macc-PA
Icon for Helper I rankHelper I
6 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

     
Macc-PA's avatar
Macc-PA
Icon for Helper I rankHelper 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
Icon for Community Support rankCommunity 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
    Icon for Helper I rankHelper 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
      Icon for Community Support rankCommunity 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

      • Macc-PA's avatar
        Macc-PA
        Icon for Helper I rankHelper I
        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