Can VAR serve two outcome either one value or a table

[admin_xlsior]

Hi,

As the subject, is where a condition that the value of VAR might be one value or many.

Take example is this measure :

My measure =

VAR IsRegionValid = NOT( ISFILTERED('Default Dimension'[Branch])

                        )

--> in this var, I check whether my report is filterer by branch

VAR selectedBranch = IF(

                        IsRegionValid,

                        CALCULATE(

                            VALUES(Organization[Branch]),

                            FILTER(Organization, Organization[Region] = SELECTEDVALUE('Default Dimension'[Region]))

                        ),

                        SELECTEDVALUE('Default Dimension'[Branch])

                    )

--> in this VAR, I if it is not filtered by branch, I want to get all the branch of the selected Region (from table Organizations), result will be many Branches

--> so if it is filtered by branch, obviosly it will only contain one branch

VAR NumofResources = CALCULATE(

                                COUNTROWS(Resource),

                                FILTER(Resource, Resource[Resource branch] in selectedBranch)

                            )

--> this is where I get an error saying "selectedBranch is not a valid table"

RETURN NumofResources

Hope it is clear pointing out, so the main idea is I want to count how many resources (worker) in either 1 branch or in 1 regions which consist of many branches.

Thanks,

[TomMartens · Accepted solution]

Hey @admin_xlsior ,

please be aware that IF can not return something different than a scalar value, meaning it can not return an object of type table, https://dax.guide/if/

Unfortunately, I do not fully understand what you are trying to achieve.

What I understand is this, depending on IsRegionValid the variable selectedBranch contains different things, but no matter what it is a table, for this reason I would rewrite things a little different.

Let the variable IsRegionValid either be 0 or 1

And the variable NumOfResources like so:

VAR NumofResources = 
CALCULATE(
	COUNTROWS(Resource)
	, FILTER(... , AND(IsRegionValid = 0 , ...)
	, FILTER(... , AND(IsRegionValid = 1 , ...)
)

Hopefully this provides you with some ideas, if you need more input, please consider to provide a pbix that contains sample data, upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom

[admin_xlsior]

Hi Tom,

Thanks for the information about IF cannot return other than scalar value. With that I just realized what went wrong, actually the CALCULATE(VALUES(....)) is wrong whereby I should use CALCULATETABLE instead. And by that I should have two VAR as well.

VAR regionBranch = CALCULATETABLE(

                            VALUES('Default Dimension'[Branch]),

                            FILTER(ALL('Default Dimension'[Region]), 'Default Dimension'[Region]= SELECTEDVALUE('Default Dimension'[Region]))

                        )

VAR selectedBranch = SELECTEDVALUE('Default Dimension'[Branch])

Thanks