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

     
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

  • Macc-PA's avatar
    Macc-PA
    Helper 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