Need to RETURN all variables in DAX query for single column in matrix

[turp111]

Hello,

I have scoured these forums for what seems like an easy solution, and I very well still might be missing something, but I haven't been able to find my answer.  Basically, I have a DAX query that's going to contain several VARs and I need to RETURN all of them.  No filters, conditions, etc.

My current DAX measure:

Categories =

VAR _particCount =

CALCULATE(

    DISTINCTCOUNT('part month end info'[Part Tax ID]),

    FILTER(

        'part month end info',

        'part month end info'[Part Month End Bal] > 0

    )

)

VAR _planUnder1 =

CALCULATE(

    DISTINCTCOUNT(

        'plan info - sql'[Plan ID]),

        FILTER(

            'plan info - sql',

            'plan info - sql'[Plan Month End Balance] > 0 && 'plan info - sql'[Plan Month End Balance] < 1000000

        )

)

VAR _planUnder3 =

CALCULATE(

    DISTINCTCOUNT('plan info - sql'[Plan ID]),

    FILTER(

        'plan info - sql',

        'plan info - sql'[Plan Month End Balance] >= 1000000 && 'plan info - sql'[Plan Month End Balance] < 3000000

    )

)

RETURN

**I want to return all the above variables**  I've tried using MAX(_particCount, _planUnder1) but I can only list two arguments here.  I'm going to have 10-15 variables tops to all show in a matrix 'column' (measure will actually go in Values section in Build pane).

Here's a pic of what I get with the two variables returned, I need all the variables returned (the bolded items are main categories in a 'Row Categories' table)

[Anonymous]

Hi @turp111 ,

Have a good day. Has your problem been solved?

Best Regards,

Wearsky

[Anonymous]

Hi @turp111 ,

A measure can only return one value. If you want to get multiple values, please try concatenating them into a string.

Best Regards,

Wearsky

[turp111]

Thank you!  Well, I was putting each category as a VAR in one measure so they all display in my matrix in one 'column' as opposed to separate ones.  I did have it this way before but when I added each measure to the Values section it created a new column.  I will review your suggestion and give it a try and response later.  

[Selva-Salimi]

hi @turp111 

although it is not that much clear for me why you need to write a measure with these variables and why didn't write measure for each of these variables, but this is the solution based on my understanding of your problem.

you can create a table which does not have relationship with other tables. write each variable name in rows and recommend to have ID's like 1,2,3 for each row. for example:

1	Participants Count
2	Active plan <1
3	Active plan < 3

then use the second column of this table in your visual and add this to the return of your measure:

if (selectedvalue( new_table(ID) = 1 , _particCount , if (selectedvalue(new_table(ID) = 2 , _planUnder1 , if ( selectedvalue(new_table( ID) =3 , _planUnder3 )))

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.