Forum Discussion

Belle2015's avatar
Belle2015
Helper II
1 year ago
Solved

Help with error when creating a measure for a date filter

Hi, 

I have had some help in creating a measure to create a filter to show the previous day's data or the weekend's data on a Monday but I am getting the below error 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value' and im not sure what I need to do to resolve this, sorry I am not so good with the more complex ones!

 

This is the table I want to filter - 

Count DateProductCounted byMore/LessDifference RecordedTotalStockPlusDiff% of Total Stock
29/01/2025623932Clerk 1More-154307-151558101.81%
29/01/2025623932Clerk 1Less154409151558101.88%
28/01/2025392-1269Clerk 4Less31543119101.12%
26/01/2025392-2036Clerk 3Less1508749220.13%
26/01/2025392-2069Clerk 3More-900018000-50.00%
22/01/2025206360Clerk 2Less343394318179.52%
21/01/2025392-1269Clerk 4More-77-13592.31%
20/01/2025203553Clerk 3More-3044113394-227.27%
19/01/20251213-2369Clerk 2More-1485-788188.45%
19/01/20252273069Clerk 2More-1200-0.50%
19/01/2025392-1036Clerk 4More-11143307-33.69%
16/01/20251213-5536Clerk 4Less555101549.50%
16/01/2025629610Clerk 4Less18351373513.36%
14/01/20251213-2369Clerk 4More-1935-1338144.62%
10/01/2025626129Clerk 1Less114183942897.97%
08/01/20251213-3636Clerk 5Less400104138.42%
08/01/20251213-5536Clerk 4More-3000-1949153.93%
08/01/20251213-5536Clerk 4More-385-334115.27%
07/01/2025392-1269Clerk 4More-53-38139.47%
04/01/2025629069Clerk 2Less34339985.96%

 

Thanks for any help 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Belle2015 

    Based on your information, I create you table. At the same time, I added a piece of data from January 6th to test it.

    Then create a new measure:

     

    PreviousDayOrWeekendData = 
VAR TodayIsMonday = WEEKDAY(MAX('Table'[Count Date]), 2) = 1
VAR _PreviousDay = CALCULATE(SUM('Table'[TotalStockPlusDiff]), 'Table'[Count Date] = MAX('Table'[Count Date]) - 1)
VAR WeekendData = CALCULATE(SUM('Table'[TotalStockPlusDiff]), WEEKDAY('Table'[Count Date], 2) IN {6, 7} && 'Table'[Count Date] <= MAX('Table'[Count Date]))
RETURN
IF(TodayIsMonday, WeekendData, _PreviousDay)

     

     

    Put this measure in table visual and create a slicer. Here is my preview:

     

    When you select a slicer, the metrics also change. At the same time, it calculates the sum of the weekends up to the date selected by the current slicer.

     

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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

7 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Belle2015 

     

    You can't return different table expressions based on conditions. Place each table expression in its own variable and then create a condition in the RETURN clause instead:

    IF(
IsMonday,
CALCULATE(SUM('Table 2'[Difference Recorded]), filter01),
CALCULATE(SUM('Table 2'[Difference Recorded]), filter02)
)
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi Belle2015 ,

    I just restructured your DAX to return the the desired scalar value, please try the bellow code and let me know if its all ok:

    FilteredByDate = 
VAR TodayDate = TODAY()
VAR DayBefore = TodayDate - 1
VAR IsMonday = WEEKDAY(TodayDate, 2) = 1
RETURN
    CALCULATE(
        SUM('Table 2'[Difference Recorded]),
        FILTER(
            'Table 2',
            IF(
                IsMonday,
                'Table 2'[Count Date] = TodayDate - 3 || 
                'Table 2'[Count Date] = TodayDate - 2 || 
                'Table 2'[Count Date] = TodayDate - 1,
                'Table 2'[Count Date] = DayBefore
            )
        )
    )
    • Belle2015's avatar
      Belle2015
      Helper II
      1 year ago

      Thanks so much for your help @Bibiano_Geraldo

      Maybe I am not using this correctly but I though that I would just apply this measure to the filters to work but it is not filtering the table! Sorry I am very basic with this and trying to learn!

       

       

      Preview
       
       
       
      • Bibiano_Geraldo's avatar
        Bibiano_Geraldo
        Super User
        1 year ago

        Hi whats is your expected resultt based on data you provided? i'll make some logic here to filter your data, just give me more context.

         

        Thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Belle2015 

    Based on your information, I create you table. At the same time, I added a piece of data from January 6th to test it.

    Then create a new measure:

     

    PreviousDayOrWeekendData = 
VAR TodayIsMonday = WEEKDAY(MAX('Table'[Count Date]), 2) = 1
VAR _PreviousDay = CALCULATE(SUM('Table'[TotalStockPlusDiff]), 'Table'[Count Date] = MAX('Table'[Count Date]) - 1)
VAR WeekendData = CALCULATE(SUM('Table'[TotalStockPlusDiff]), WEEKDAY('Table'[Count Date], 2) IN {6, 7} && 'Table'[Count Date] <= MAX('Table'[Count Date]))
RETURN
IF(TodayIsMonday, WeekendData, _PreviousDay)

     

     

    Put this measure in table visual and create a slicer. Here is my preview:

     

    When you select a slicer, the metrics also change. At the same time, it calculates the sum of the weekends up to the date selected by the current slicer.

     

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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

    • Belle2015's avatar
      Belle2015
      Helper II
      1 year ago

      Thanks for your help with this, I think m not really explaining what I wanted it to do very well, I was hoping that it would work a bit like the relative date filter but without having to go in and select the days I wanted