Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alejotecelec
Frequent Visitor

A table of multiple values was supplied where a single value was expected. Two Filters same table

Hello everyone, 

I am trying to apply two filters to the same table ( Gifts ) both of them "Datesbetween" into a "caculatetable" but the error "<ccon>A table of multiple values was supplied where a single value was expected.</ccon>" prevents me to continue.

 

Table = FILTER(
            GROUPBY(
                CALCULATETABLE(
                        Gifts,
                        DATESBETWEEN(Gifts[Gift_Date],
                        DATE(2021,04,01),
                        DATE(2022,03,31)
                        &&
                        DATESBETWEEN(Gifts[Date_Added_Donor],
                        DATE(2021,04,01),
                        date(2022,03,31))
                ))
                ,Gifts[Constituent ID],
                "Total_Gifts",
            COUNTX(CURRENTGROUP(),COUNT(Gifts[Gift Type]))), 
        [Total_Gifts] > 1)

 

2 REPLIES 2
selimovd
Super User
Super User

Hey @Alejotecelec ,

 

for me it looks like the brackets in the datesbetween are wrong. You didn't close the brackets of the first DATESBETWEEN.

Try that one:

Table =
FILTER (
    GROUPBY (
        CALCULATETABLE (
            Gifts,
            DATESBETWEEN (
                Gifts[Gift_Date],
                DATE ( 2021, 04, 01 ),
                DATE ( 2022, 03, 31 )
            )
                && DATESBETWEEN (
                    Gifts[Date_Added_Donor],
                    DATE ( 2021, 04, 01 ),
                    DATE ( 2022, 03, 31 )
                )
        ),
        Gifts[Constituent ID],
        "Total_Gifts",
            COUNTX (
                CURRENTGROUP (),
                COUNT ( Gifts[Gift Type] )
            )
    ),
    [Total_Gifts] > 1
)

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

Hi Denis, 

I've tried the solution and now I have this message " A function 'DATESBETWEEN' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

 

 

Table = FILTER(
            GROUPBY(
                CALCULATETABLE(
                    Gifts,
                    DATESBETWEEN(
                        Gifts[Gift_Date],
                        DATE(2021,04,01),
                        DATE(2022,03,31)
                    )
                        &&
                        DATESBETWEEN(
                            Gifts[Date_Added_Donor],
                            DATE(2021,04,01),
                            date(2022,03,31)
                        )
                ),
                Gifts[Constituent ID],
                "Total_Gifts",
                    COUNTX(
                        CURRENTGROUP(),
                        COUNT(Gifts[Gift Type])
                    )
            ),
        [Total_Gifts] > 1
    )

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors