Storing multiple value's in a variable?

[Joshmarsteffens]

Hello,

I have a question regarding datamanipulation. I am trying to get data from 'Leenotification' to 'KPI sociale doelstellingen' however this must be linked by its Complex/Kavel ID. Meaning there will be multiple numbers returned through the lookupvalue() function (this is the case because 'KPI sociale doelstelling' is linked with 'data_WBS_tbv_beleidsp.' through kavel/object id which is a one on one relation, the same goes for the relation between 'woning_overzicht' and 'data_WBS_tbv_beleidsp.' however from 'woning_overzicht' to 'PMEOBJECT' is a one to many relation ship).

But i can not use a count() to count it since count() only counts columns and not variable numbers. Also im not even certain if a variable can contain multiple values in DAX syntax. Is there maybe an array varriable that I could use? Anyways I had come up with this syntax but it gives me nothing as a result.

Hoeveelheid overlastmeldingen = var connectionDataWBS = LOOKUPVALUE(Data_WBS_tbv_Beleidsportaal[Uniek nummer]; Data_WBS_tbv_Beleidsportaal[Uniek nummer]; 'KPI sociale doelstelling'[Kavel ID])
var connectionWoningOverzicht = LOOKUPVALUE(Woning_overzicht[complex]; Woning_overzicht[complex]; connectionDataWBS)
var connectionPMEOBJECT = LOOKUPVALUE(PMEOBJECT[OBJECTID]; PMEOBJECT[COMPLEXID]; connectionWoningOverzicht)
var connectionLEENOTIFICATION = LOOKUPVALUE(LEENOTIFICATION[NOTIFICATIONID]; LEENOTIFICATION[OBJECTID]; connectionPMEOBJECT )
return
connectionLEENOTIFICATION

(From variable connectionPMEOBJECT it already returns BLANK() values since it starts there with the one to many relationship)

This probably has to do with the fact that im trying to store multiple value's in a variable. I would want to set it in an new column to temporary store those values however I need those still to be linked to the Complex/Kavel ID meaning I would still get multiple values in a variable.

I hope that someone could help me with this one.

Greetings,

Joshmar Steffens

[v-huizhn-msft]

Hi @Joshmarsteffens,

>>But i can not use a count() to count it since count() only counts columns and not variable numbers.

You can use COUNTA function, which counts the number of cells in a column that are not empty. 

A variable can include a simple table, just like the following example DAX formula.

[RedSalesLastYear] :=
VAR
    RedProducts = FILTER (
        ALL ( Product[Color] ),
        Product[Color] = "Red"
    )
VAR
    LastYear = SAMEPERIODLASTYEAR ( 'Date'[Date] )
RETURN
    CALCULATE ( SUM ( Sales[Quantity] ), RedProducts, LastYear )

For your requirement, it's difficult to include mutiple values, because the rows returned by LOOKUPVALUE are different.

Best Regards,
Angelia

[v-huizhn-msft]

Hi @Joshmarsteffens,

Have you resolved your issue? If you have, please mark corresponding reply as answer, and welcome to share your own solution. 

If you have other problem, please feel free to ask.

Best Regards,
Angelia

[Joshmarsteffens]

Hello @v-huizhn-msft,

Sadly this has not fixed my issue 

The problem I have is, that I have a distinct id in the target table however the data I get comes from a table that doesnt have distinct numbers on that same column. (so the columns used in the condition for the LOOKUPVALUE())

The result of this is that when you have ID 1 it may or may not return 6, 92, 199 ,32 but what i would want it to return is 4, the count of the results.

However I can't use ANY of the counts as it will just result in an error saying that it needs a column or an table or rows to be able to count. I can not use any of the counts over an varriable.

A collega of mine came with an sollution. In my case the SUMMARIZE() function can be used here to get the results I wanted to see.