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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

A table of multiple values was supplied where a single value was expected !

Hi everyone!

I developed a calculated column with the below formula and it works correctly.

Call Off Description = DISTINCT (UNION (
CALCULATETABLE (vALUES ('Employee Hours'[ WO Name] ),FILTER('Employee Hours',ISBLANK ( ' Employee Hours'[Code] ) = FALSE () && 'Employee Hours'[Code] ='Call off Information'[Call Off Codes]))
,CALCULATETABLE (VALUES ( 'Asset Hours'[A WO Name] ),FILTER ( 'Asset Hours', ISBLANK ( 'Asset Hours'[Code] ) = FALSE () && ' Asset Hours'[WorkOrderCode]='Call off Information'[Call Off Codes]))))

 

Then I tried to add another source of data to it and extend the formula as per below:

 

Call Off Description = DISTINCT (UNION (
CALCULATETABLE (vALUES ('Employee Hours'[ WO Name] ),FILTER('Employee Hours',ISBLANK ( ' Employee Hours'[Code] ) = FALSE () && 'Employee Hours'[Code] ='Call off Information'[Call Off Codes]))
,CALCULATETABLE (VALUES ( 'Asset Hours'[A WO Name] ),FILTER ( 'Asset Hours', ISBLANK ( 'Asset Hours'[Code] ) = FALSE () && ' Asset Hours'[WorkOrderCode]='Call off Information'[Call Off Codes]))

,CALCULATETABLE (VALUES ( 'cost'[ Name] ),FILTER ( 'cost', ISBLANK ( 'cost'[Code] ) = FALSE () && ' cost'[Code]='Call off Information'[Call Off Codes]))

 

I received "A table of multiple values was supplied where a single value was expected "

 

any idea ?

 

 

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

Seems your formula will return multiple values.

So you can use a variable to store the values....then use CONCATENATEX to combine them

 

May be this one

 

Call Off Description =
VAR temp =
    DISTINCT (
        UNION (
            CALCULATETABLE (
                VALUES ( 'Employee Hours'[ WO Name] ),
                FILTER (
                    'Employee Hours',
                    ISBLANK ( ' Employee Hours'[Code] ) = FALSE ()
                        && 'Employee Hours'[Code] = 'Call off Information'[Call Off Codes]
                )
            ),
            CALCULATETABLE (
                VALUES ( 'Asset Hours'[A WO Name] ),
                FILTER (
                    'Asset Hours',
                    ISBLANK ( 'Asset Hours'[Code] ) = FALSE ()
                        && ' Asset Hours'[WorkOrderCode] = 'Call off Information'[Call Off Codes]
                )
            )
        )
    )
RETURN
    CONCATENATEX ( temp, [ WO Name], ", " )

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Thanks Zubair_Muhammad for your time and response.

 

my first formula and your formula both work correctly. The issue appears when I want to add another source of data to the formula.

 

Long story short, call off description returns a few blank values because a few of codes aren't in  employee hours table or Asset Hours table. as a result, formula cant find any description!

 

I  want to modify the formula in a way to pull overs call off description from another table named "cost table" JUST for those codes that are not in other two tables. 

can you think of any solution? Thanks in advance

 

@Anonymous

 

What results do you get when you use CONCATENATEX approach in the second formula?

Also I think you can use Calculated Tables to debug your formula

For example from the Modelling tab >> New Table...you can check the results for

 

Calculated Table =
CALCULATETABLE (
    VALUES ( 'cost'[ Name] ),
    FILTER (
        'cost',
        ISBLANK ( 'cost'[Code] ) = FALSE ()
            && ' cost'[Code] = 'Call off Information'[Call Off Codes]
    )
)

 

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

I get an error message that "A table of multiple values was supplied where a single value was expected" ! and I cant undrestand why!

@Anonymous

 

Could you share your file?


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

I sent it to your email.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.