In list of values as measure filter

[AGo]

Hi,

I'm trying to return a list with a measure and then count the primary keys of a table in that list.

The ListMeasure cannot be visualized in any visual but has not notified errors.

ListMeasure = CALCULATE(VALUES(PBI100_TOTAL[B10KEY]);PBI100_TOTAL[delivery date] in CALENDAR("2019-01-01";"2019-05-31"))

Then I tried to use that measure in this other measure, but the argument in this format is not allowed:

Count = COUNTROWS(FILTER(PBI100_TOTAL;PBI100_TOTAL[B10KEY] in [ListMeasure]))

Any suggests to correct it? The logics are much more complicated, and I need to use measures because I can't use filter as a parameter in a calculated column/calculated table, but this is the only one problem remained.

Thanks in advance

[AlB]

Hi @AGo 

I'm not sure I understand completely but two things stand out:

1. Looks like you would need CALCULATETABLE in [ListMeasure]. I assume the list can be more than just one row.

2. Why do you need the measure at all in 'Count'? How about

Count =
COUNTROWS (
    FILTER (
        PBI100_TOTAL;
        PBI100_TOTAL[Delivery Date] IN CALENDAR ( "2019-01-01"; "2019-05-31" )
    )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[AGo]

Tried CALCULATETABLE but no important changes: Can't I write IN and then a measure that returns a multi-rows list? Is there a workaround for this? 

Consider that:

I need the ListMeasure because I will use it in a lot of other measures,

the arguments of CALENDAR will be substituted with a MIN and MAX functions,

I will make another function ListMeasure2, similar but with other date conditions.

Thanks @AlB 

[AlB · Accepted solution]

I'm a bit lost. A measure cannot return a table, only a scalar, so that would be the first issue. 

IN seems to require a table expression as second argument so, since a measure can only be a scalar, it makes sense that it doesn't admit it.  Maybe if you provide some sample data and describe exactly what count is supposed to do (you mention MIN, MAX) we can find a workaround 

[AGo]

Thank you, this is a solution yet. I was lost because using Values or Calculatetable (that has a table as output as from the MS documentation) in a measure I was not encountering any error, but you specified measures won't return anything except scalar values. The problem is that only measures are directly affected by filters and I can't use a visual filter to make conditions on a calculated table or column as a parameter. This is a big limit in Power BI, but thank you very much anyway

[AlB]

You won't get an error as you type the code if you use CALCULATETABLE because it is possible that the table returned is a one-row, one-column table (which is basically a scalar). It will only fail at runtime if the value returned is not a scalar. In any case, if you care to explain more in detail (with an example) what it is that you need maybe we can find a solution.

Cheers