topic Re: In VAR, return should be a table in DAX Commands and Tips

In VAR, return should be a table

Brani — Fri, 30 Jun 2023 10:22:29 GMT

Hi all

I by my self have a issue to explain what the issues is. At least I know what I need:

I have a set of conditions in Variables:

__Test_Successful_Status_ =
VAR Switchover = if(Query1[SwitchoverDate] = BLANK() && Query1[Status] = "Successful", "SwitchoverDate_must be filled out, ", BLANK())
VAR Switchback = if(Query1[SwitchbackDate] = BLANK() && Query1[Status] = "Successful", "SwitchbackDate_must be filled out, ", BLANK())
VAR StatusReason = if(Query1[StatusReason] = BLANK() && Query1[Status] = "Successful", "StatusReason_must be filled out, ", BLANK())

Return
Switchover & Switchback & StatusReason

When I add this to a column, I receive OR a single value OR a concatenate string of value. So far so good.

Here I need your help: How to get a) a list of all the individual values and b) a count of all values.

Many thanks
Brani

Re: In VAR, return should be a table

Martin_D — Fri, 30 Jun 2023 15:31:16 GMT

Hi Brani ,

A list of all the individual values (including blanks) is:

VAR _list = { Switchover, Switchback, StatusReason }

A list of all the individual values except blank values is:

VAR _listWithoutBlanks = FILTER ( _list, NOT ( ISBLANK ( [Value] ) ) )

A count of all values except blanks is:

VAR _countNoBlanks = COUNTAX ( _list, [Value] )

A count of all values including blanks is (actually it's always 3 because you know you created a list from 3 elements):

VAR _countAll = COUNTROWS ( _list )

Martin

Re: In VAR, return should be a table

Brani — Mon, 03 Jul 2023 05:48:03 GMT

Hi Martin_D

Thank you for replying.

I've added "Var List_" to my query but now it is showing "A table of multiple values was supplied where a single value was expected."

__Test_Successful_Status_ = VAR Switchover = if(Query1[SwitchoverDate] = BLANK() && Query1[Status] = "Successful", "SwitchoverDate_must be filled out, ", BLANK()) VAR Switchback = if(Query1[SwitchbackDate] = BLANK() && Query1[Status] = "Successful", "SwitchbackDate_must be filled out, ", BLANK()) VAR StatusReason = if(Query1[StatusReason] = BLANK() && Query1[Status] = "Successful", "StatusReason_must be filled out, ", BLANK()) VAR List_ = { Switchover , Switchback , StatusReason } Return List_

When trying to summarize the table, I've got this error message:

Can you advice?

Many thanks
Brani

Re: In VAR, return should be a table

Martin_D — Mon, 03 Jul 2023 07:12:51 GMT

Hi Brani ,

A measure cannot return a list or table, a measure can only return a single value. You could return e.g. the variables _countNoBlanks or _countAll. What you call a "list" is, maybe, not technically a list in terms of the DAX programming language, but a visual on a report table? Maybe a table visual?

For less reply ping-pong, would you mind providing the following:

The file
A description of the business requirements (not just the latest technical problem that you are facing)
A screenshot of what you currently see on your report page and what you don't like about it resp. what you are missing
A screenshot of a draft of what you finally want to see

Martin

Re: In VAR, return should be a table

Brani — Tue, 04 Jul 2023 05:05:42 GMT

Hi all

I am in need of your help. I've created a screenshot with the draft/outcome I would need: