Forum Discussion

acg's avatar
acg
Icon for Resolver I rankResolver I
4 years ago
Solved

Where Answer is 'Yes'

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
)
  • Anonymous's avatar
    Anonymous
    4 years ago

    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
)

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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
)
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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"

     

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.