Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StefRenneboog91
Regular Visitor

BEGINNER LEVEL - Use measure within other measure

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
1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@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.

View solution in original post

8 REPLIES 8
V-pazhen-msft
Community Support
Community Support

@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
Regular Visitor

@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

Hi @StefRenneboog91 ,

 

What is your measure calculation?

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hi @Pragati11 ,

Measure HEADCOUNTM = 

SUMX(
Filter(
PA0000,
PA0000[BEGDA] <= SELECTEDVALUE('Date'[Date],TODAY()) &&
PA0000[ENDDA] >= SELECTEDVALUE('Date'[Date],TODAY())
),PA0000[Active]
)

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.

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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

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
    )
)

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.