Latest Answer of an item in fact table

[MarkCBB]

Hi There,

I have the following DAX Measure:

MB HOME RUN = DIVIDE(CALCULATE(COUNTROWS(FORMS),FORMS[sQuestion]="MBHR",FORMS[sAnswer]="yes",LASTDATE('CALENDAR'[Date])),CALCULATE(COUNTROWS(FORMS),FORMS[sQuestion]="MBHR",LASTDATE('CALENDAR'[Date]),FORMS[sAnswer]<>""),0)

The MBHR column contains Yes/No and Blank answers. I am trying to get the latest answer from the database for each store. 

A store could have an answer Yes on the 1st of the month and an answer No on the 2nd of the Month, if No, it should return 0 and if yes it should return 100%. 

The calculation above brings back other answers i.e. 25%, thus it brings back 4 transaction, one of which is a yes the rest are no. 

I just want the latest one per store. 

[GilbertQ]

What I would suggest doing is to break all your measures above apart so that you can make sure each measure is doing what you expect it to.

Then for the final measure I would use the LASTNONBLANK, as this will always go and get the last non blank value and will not rely solely on the date.

[MarkCBB]

Hi @GilbertQ  as far as I understand LASTNONBLANK return the last non-blank row in the table. I am using Direct Query and thus not able to sort the table by the date, so the last non-blank row might not be the last date. This is how I understand it.

I will break the measure apart now and get back to you during the day.

Thanks

[GilbertQ]

Ok I was unaware that you are using Direct Query, which means you can potentially be limited by what DAX functions you can use.

If possible are you able to create a column in your source database which will be able to identify which one is the last one and then your measure will be a lot easier to find?

[MarkCBB]

@GilbertQ Sorry I should have mentioned I was using DQ (First time using DQ). Unfortunately, I am not able to edit the data at all. 

[MarkCBB]

Hello @GilbertQ

thank you for you for your help, I have seemed to solve the challenge.  I am able to return the % per outlet/store 0% = No, 100% = Yes.

However, I have now run into the next challenge, to get the average of the yes's (yes/(yes+no) for the latest visits per outlet/store.

I have created a simple table to better explain what I am looking for:

        

in the above table I have highlighted the latest visit/transaction per outlet, i.e. the last transaction for Place 08 was on the 1st June. whereas the last transaction for Place 10 was on the 3rd.

This table shows the results I am looking for, mainly the yes %. 

based on the latest transaction for each store there are 5 yes's and 5 No's, thus the yes % is 50%. I want to put that Yes % into a car visual.

Here is a link to the example data:

DATA

[GilbertQ]

Hi there

just so I understand you want to now get a count of Yes/No using the last time they recorded an answer?

[MarkCBB]

Correct, out of those 10 stores/outlets, based on the last date, what is the percentage yes.

Date format is YYYY-MM-DD

[MarkCBB · Accepted solution]

@GilbertQ,

Got it working 🙂

COUNT NO = CALCULATE(COUNTROWS(DATA),FILTER(DATA,DATA[DATE]=[LATEST]),DATA[ANSWER]="NO")

COUNT YES = CALCULATE(COUNTROWS(DATA),FILTER(DATA,DATA[DATE]=[LATEST]),DATA[ANSWER]="YES")

LATEST = CALCULATE(MAX(DATA[DATE]),ALLEXCEPT(DATA,DATA[OUTLET NAME]))

and then 

% = DIVIDE([COUNT YES],[COUNT YES]+[COUNT NO],0)

I am pretty sure there is a better way to do the above - but it does seem to work.

[v-chuncz-msft]

@MarkCBB,

You could also refer to the following DAX.

% =
DIVIDE (
    COUNTROWS (
        FILTER (
            VALUES ( DATA[OUTLET NAME] ),
            CALCULATE (
                CONTAINS ( DATA, DATA[DATE], MAX ( DATA[DATE] ), DATA[ANSWER], "YES" )
            )
        )
    ),
    COUNTROWS ( VALUES ( DATA[OUTLET NAME] ) )
)

[MarkCBB]

@v-chuncz-msft,

Thank looks nice and clean, but for some reason, I get a difference of 00.43% from my measure to your measure. Why would that be? which one is correct?

Here is the raw data. 

DATA

It would appear mine is counting an addition NO or your measure is counting 1 less NO.

[v-chuncz-msft]

@MarkCBB,

My formula also counts blank value.

[MarkCBB]

ahhh, I see, thank you