topic Re: Where Answer is 'Yes' in DAX Commands and Tips

Where Answer is 'Yes'

acg — Fri, 25 Mar 2022 07:52:23 GMT

I want to calculate the number of Entities that had international Presence (= INT) in the last Year.

International Presence is determined by answering to [Question Code] = "Q0035" with [Answer] = "Yes"

As such the below calculation works EXCEPT when I add the filter

'Fact QNA'[Answer] = "Yes"

Is there a different way in DAX to introduce the "where 'Fact QNA'[Answer] = "Yes" ?

Number INT Presence YearMax =

VAR MAXSD = CALCULATE(MAX('Dim Submission Date'[date]),ALLSELECTED('Dim Submission Date'))

VAR MAXYSD = CALCULATE(MAX('Dim Submission Date'[year]),'Dim Submission Date'[date] = MAXSD)

RETURN

CALCULATE

(

COUNT('Fact QNA'[Companies]),

'Fact QNA'[Answer] = "Yes",

'Dim Questions'[Question Code] = "Q0035",

'Dim Submission Date'[year] = MAXYSD

)

Re: Where Answer is 'Yes'

Anonymous — Fri, 25 Mar 2022 08:22:16 GMT

Hi acg
can you please try

Number INT Presence YearMax = VAR MAXSD = CALCULATE(MAX('Dim Submission Date'[date]),ALLSELECTED('Dim Submission Date')) VAR MAXYSD = CALCULATE(MAX('Dim Submission Date'[year]),'Dim Submission Date'[date] = MAXSD) RETURN CALCULATE ( COUNT('Fact QNA'[Companies]), 'Fact QNA'[Answer] = "Yes" && 'Dim Questions'[Question Code] = "Q0035", 'Dim Submission Date'[year] = MAXYSD )

Re: Where Answer is 'Yes'

tamerj1 — Fri, 25 Mar 2022 08:40:22 GMT

Hi acg
you may try

Number INT Presence YearMax = VAR MAXYSD = MAX ( 'Dim Submission Date'[year] ) RETURN CALCULATE ( COUNT ( 'Fact QNA'[Companies] ), 'Fact QNA'[Answer] = "Yes", FILTER ( 'Dim Questions', 'Dim Questions'[Question Code] = "Q0035" ), FILTER ( 'Dim Submission Date', 'Dim Submission Date'[year] = MAXYSD ) )

Also it is worth it toc check the source data for possible spaces after or before "Yes"

Re: Where Answer is 'Yes'

v-xiaotang — Tue, 29 Mar 2022 10:11:58 GMT

Hi acg

I just want to confirm have you tried the solution shared by tamerj1 and Aditya_Meshram ? Do they help?

If you still need help, usually information below is needed, could you provide it? Thanks.

(1) a sample file, you can replace raw data with bogus data to protect your privacy.

or provide some sample data that fully covers your issue/question.

(2) give your expected result based on the sample you provide. If calculation formula is involved, please provide it.

Kindly note: Please ensure the data in sample is concise and representative.

Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.