Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to Solution.
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.
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 Sorry I should have mentioned I was using DQ (First time using DQ). Unfortunately, I am not able to edit the data at all.
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:
Correct, out of those 10 stores/outlets, based on the last date, what is the percentage yes.
Date format is YYYY-MM-DD
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.
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] ) ) )
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.
It would appear mine is counting an addition NO or your measure is counting 1 less NO.
My formula also counts blank value.
ahhh, I see, thank you
User | Count |
---|---|
141 | |
70 | |
70 | |
53 | |
53 |
User | Count |
---|---|
208 | |
95 | |
64 | |
61 | |
57 |