Forum Discussion

Joshmarsteffens's avatar
Joshmarsteffens
Frequent Visitor
9 years ago

Storing multiple value's in a variable?

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 th...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Joshmarsteffens
      Frequent Visitor
      9 years ago

      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.