Forum Discussion

powerlight1's avatar
powerlight1
Helper I
11 months ago
Solved

How to Filter Using a Separate Table

Hi Community,

I am doing some complex calculation and "OR Condition" filtering and need some help.

 

I have a simple table called Baseline with date and sales:

DateBaseline Sales
1/11/202416.8658
2/11/20243.514709
3/11/202495.39742
4/11/202453.36697
5/11/202468.06059
6/11/202466.81327
7/11/202471.91321
8/11/202485.36628
9/11/20241.358787
10/11/202434.57078

 

and I have a second table called Actual sales:

DateActual Sales
1/11/202492.65061
2/11/202487.16787
3/11/202477.94617
4/11/202465.8121
5/11/202454.01474
6/11/202496.31909
7/11/202440.95989
8/11/202415.41474
9/11/202442.09341
10/11/202423.39248
11/11/202429.6792
12/11/202450.02887
13/11/202461.36338
14/11/202448.28854
15/11/202418.21006

 

The relationship between the two tables is just the "Date" column

In addition I've also created 2 independent date table to use as date slicers :

 

slicer_dates_BL =
SELECTCOLUMNS(
    Baseline
   ,"Date"
    ,[Date])
 
slicer_dates_ACT =
SELECTCOLUMNS(
    Actual
   ,"Date"
    ,[Date])
 
I then created a Measure to use as my filter:
 
Filter_Date =
IF(
    MAX(Baseline[Date]) IN ALLSELECTED(slicer_dates_BL[Date])  
    ,1
    ,0
) 
 

What I then want to show in a table the following:

  1. The average sales from the first 5 days from the Baseline table for period 01.11.2024 to 05.11.2024
  2. The average sales from the Actual table for period 11.11.2024 to 15.11.2024

 

For objective 1. above, I created the 2 Measure:

AVG_Baseline_Sales =
CALCULATE(
    AVERAGE(Baseline[Baseline Sales])
)

 

AVG_Baseline_Sales2 =
CALCULATE(
    AVERAGE(Baseline[Baseline Sales])
    ,FILTER(
        Baseline
        ,MAX([Date]) in ALLSELECTED(slicer_dates_BL[Date])
    )
)
 
 

I then place a table on my canvas placed Filter_Date  in the Filter section of the table and set to equal 1. 

I then set my baseline date slicer to the period 01.11.2024 to 05.11.2024

 

I then placed the both measures AVG_Baseline_Sales and AVG_Baseline_Sales2  into the table. 

 

You can see from the screen shot below AVG_Baseline_Sales is calculate the average for the entire 10days and not just the first 5 days, whereas AVG_Baseline_Sales2 is giving me blank.  How do I just show the average of the first 5 days sales without having to place the field "Date" into my results table?

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    11 months ago

    Hi  , 

    For this I believe you do not need to have two calendar tables.

     

    What I modeled was the following:

    • Created a calendar table
      • Needs to start at 1st of January of the minimum year and end at 31 of December of maximum year
      • Be carefull to check the dates between both the sales and the baseline
      • In this case the calendar goes from January 1st 2024 to  31st December 2024
      • Create a one to many between the tables:
        • Calendar - Baseline
        • Calendar - Actual
        • Create the measures below:
          • AVG_Baseline_Sales = CALCULATE( AVERAGE(Baseline[Baseline Sales]), ALL('Calendar'[Date] )) AVG_Baseline_Sales2 = CALCULATE( AVERAGE(Baseline[Baseline Sales]) )Use the calendar table in your slicer
          • Result below

             

            The relationship will pick up the baselines corretly now you can also do a average of the sales and add the date to the visualization:

             

            If you want to keep the average for the 5 days you can create a measure similar to this one:AVG_Baseline_Sales 3 = CALCULATE( AVERAGE(Baseline[Baseline Sales]), ALLSELECTED('Calendar'[Date] )) 

            Please see attach file.

             

              

              

             

             

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    11 months ago

    Hi  , 

    For this I believe you do not need to have two calendar tables.

     

    What I modeled was the following:

    • Created a calendar table
      • Needs to start at 1st of January of the minimum year and end at 31 of December of maximum year
      • Be carefull to check the dates between both the sales and the baseline
      • In this case the calendar goes from January 1st 2024 to  31st December 2024
      • Create a one to many between the tables:
        • Calendar - Baseline
        • Calendar - Actual
        • Create the measures below:
          • AVG_Baseline_Sales = CALCULATE( AVERAGE(Baseline[Baseline Sales]), ALL('Calendar'[Date] )) AVG_Baseline_Sales2 = CALCULATE( AVERAGE(Baseline[Baseline Sales]) )Use the calendar table in your slicer
          • Result below

             

            The relationship will pick up the baselines corretly now you can also do a average of the sales and add the date to the visualization:

             

            If you want to keep the average for the 5 days you can create a measure similar to this one:AVG_Baseline_Sales 3 = CALCULATE( AVERAGE(Baseline[Baseline Sales]), ALLSELECTED('Calendar'[Date] )) 

            Please see attach file.

             

              

              

             

             

  • v-dineshya's avatar
    v-dineshya
    Community Support
    11 months ago

    Hi powerlight1 ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Please follow below steps.

     

    1. Created Actual and Baseline tables based on your input data.

     

    2. Created disconnected tables  Slicer_dates_BL and Slicer_dates_ACT for slicers.

     

    3. Created measures Avg_Baseline_Sales and Avg_Actual_Sales.

     

    4. In Table visual, I have dragged measues and in slicers, i have choosed '1/11/2024' to '5/11/2024' in Slicer_dates_BL and '11/11/2024' to '15/11/2025' in Slicer_dates_ACT .

     

    Please refer below output snap and attached PBIX file.

     

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

    • v-dineshya's avatar
      v-dineshya
      Community Support
      11 months ago

      Hi powerlight1 ,

      We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

       

      Regards,

      Dinesh

      • v-dineshya's avatar
        v-dineshya
        Community Support
        11 months ago

        Hi @powerlight1 ,

        We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

         

        Regards,

        Dinesh

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    11 months ago

    Hi,

    Based on the 2 tables that you have shared, show the expected result very clearly.