BEGINNER LEVEL - Use measure within other measure

[StefRenneboog91]

Hey guys,

Thanks already for the effort for answering my question.

I created a measure "HEADCOUNTM" and I want to use this measure within this measure :

NoIT1 =

CALCULATE(

DISTINCTCOUNT(

PA0000[PERNR]

),

PA0000[BEGDA] <= TODAY(),

PA0000[ENDDA] >= TODAY(),

(PA0001[PERSG] = 1 || PA0001[PERSG] = 2 || PA0001[PERSG] = 4),

PA0000[IT1] = 0,

HEADCOUNTM = 1

)

This is giving me the error : 

A FUNCTION CALCULATE HAS BEEN USED IN A TRUE/FALSE EXPRESSION THAT IS USED AS A TABLE FILTER EXPRESSION. THIS IS NOT ALLOWED.

While if I remove the HEADCOUNTM = 1 out of the filter , the error is gone.

Thanks a lot already ! 

KR,

Stef

[Anonymous · Accepted solution]

@StefRenneboog91 

Try change the HEADCOUNTM to the following, by the you wnt SUM or Count? 

HEADCOUNTM = Calcuate(sum([Active]),
Filter(PA0000,

PA0000[BEGDA] <= SELECTEDVALUE('Date'[Date],TODAY()) &&

PA0000[ENDDA] >= SELECTEDVALUE('Date'[Date],TODAY()))

Then stick with your orginial formula as below:

NoIT1 =

CALCULATE(

DISTINCTCOUNT(

PA0000[PERNR]

),

PA0000[BEGDA] <= TODAY(),

PA0000[ENDDA] >= TODAY(),

(PA0001[PERSG] = 1 || PA0001[PERSG] = 2 || PA0001[PERSG] = 4),

PA0000[IT1] = 0,

HEADCOUNTM = 1

)

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[StefRenneboog91]

@Pragati11 ,

Thanks a lot for your effort even though this is not solving the problem.
If I just copy paste your code, I'm getting an error 🙂

"A single value for column PERSG in table "PA0001" cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.

Thanks again,

KR,

Stef

[Pragati11]

Hi @StefRenneboog91 ,

What is your measure calculation?

[StefRenneboog91]

Hi @Pragati11 ,

Measure HEADCOUNTM = 

SUMX(

Filter(

PA0000,

PA0000[BEGDA] <= SELECTEDVALUE('Date'[Date],TODAY()) &&

PA0000[ENDDA] >= SELECTEDVALUE('Date'[Date],TODAY())

),PA0000[Active]

)

[Pragati11]

Hi @StefRenneboog91 ,

If I see your HEADCOUNTM measure, it is calculated at a row level and in the other DAX above you are using this measure without any summarisation like MAX, MIN, etc. as it will return multiple values.

So, in your DAX where you are getting error, use an aggregation funtion on your HEADCOUNTM measure and then try it.

[StefRenneboog91]

Hi @Pragati11 ,

This is my code at this moment :

NoIT1 =

CALCULATE(

DISTINCTCOUNT(

PA0000[PERNR]

),

PA0000[BEGDA] <= TODAY(),

PA0000[ENDDA] >= TODAY(),

(PA0001[PERSG] = 1 || PA0001[PERSG] = 2 || PA0001[PERSG] = 4),

PA0000[IT1] = 0,

MIN([HeadcountM]) = 1

)

So as you can see I added MIN() statement at my measure, unfortunately I'm still getting :

A function 'MIN' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

KR,

Stef

[Pragati11]

Hi @StefRenneboog91 ,

Use this aggregation with the modification I suggested earlier to your DAX:

NoIT1 =
CALCULATE (
    DISTINCTCOUNT ( PA0000[PERNR] ),
    FILTER (
        ALL ( PA0000 ),
        PA0000[BEGDA] <= TODAY ()
            && PA0000[ENDDA] >= TODAY ()
            && ( PA0001[PERSG] = 1
            || PA0001[PERSG] = 2
            || PA0001[PERSG] = 4 )
            && PA0000[IT1] = 0
            && MIN(HEADCOUNTM) = 1
    )
)

[Pragati11]

Hi @StefRenneboog91 ,

Try modifying your DAX as follows:

NoIT1 =
CALCULATE (
    DISTINCTCOUNT ( PA0000[PERNR] ),
    FILTER (
        ALL ( PA0000 ),
        PA0000[BEGDA] <= TODAY ()
            && PA0000[ENDDA] >= TODAY ()
            && ( PA0001[PERSG] = 1
            || PA0001[PERSG] = 2
            || PA0001[PERSG] = 4 )
            && PA0000[IT1] = 0
            && HEADCOUNTM = 1
    )
)

I am assuming that all the filter columns used in your expression which are separated by a comma mean AND condition.

Thanks,

Pragati