Latest Distinct Count of one column if another column 1 and to not count when the column value is 0

[Vishwanathraoyl]

So, I have a problem.

There are about 20 KPIs I'm trying to build based on surveys where I need to have a unique count of outlet-id's if the survey response is 1. The caveat is that I need to capture the latest value only and if the latest value is 0 even if the previous value is 1, The outlet needs to be skipped in the count.

Is there a way to do this?

I'm currently using this dax measure:

KPI1= CALCULATE(DISTINCTCOUNT(MasterTable_Core[OutletID]),MasterTable_Core[Date],
FILTER (
CALCULATETABLE (
SUMMARIZE (
'MasterTable_Core',
'MasterTable_Core'[OutletID],
'MasterTable_Core'[Date],
"IsLatest", IF (
'MasterTable_Core'[Date]
= CALCULATE (
LASTDATE('MasterTable_Core'[Date]),
ALLEXCEPT ( 'MasterTable_Core', 'MasterTable_Core'[outletid],MasterTable_Core[OutletName],MasterTable_Core[FiscalMonth] )
),
1
)
),
MasterTable_Core[KPI1] = 1
),
[IsLatest] = 1
)
)

[v-chuncz-msft]

@Vishwanathraoyl,

You may use RANKX Function to add a calculated column first.

http://community.powerbi.com/t5/Desktop/Was-this-the-last-payroll-transaction-this-month/m-p/309301#...

[Vishwanathraoyl]

@v-chuncz-msft Thank you for the reply!, But that works in the same way my code does.

The problem I'm facing is a bit odd. 

Below is an example: I have 12 outlets, visited by a sales rep for 7 days and they take the same survey in the outlets each time.

I should take only the latest record, whether it is a zero or a one and then take a sum of that to find the KPI Value.

What all the DAX queries are doing is essentially taking the last non zero value or taking a sum at outlet level and giving that as one. Any ideas on how I could handle this?

Thanks for taking the time!