In VAR, return should be a table

[Brani]

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

[Brani]

Hi all

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

[Martin_D]

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 )

BR

Martin

[Brani]

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

[Martin_D]

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

BR

Martin