I have SQL like this
select count(*) from myTABLE f1 join myTABLE f2
on f1.customerid = f2.customerid
where f1.reportingdate='2017-10-31' and f2.reportingdate='2017-10-30'
and f1.currentstatus='1' and f2.currentstatus <>'1'
I want to get this as a measure in PowerBI desktop.
The dates should be dynamic from a date heirachy slider (table already created)
How can i get this is DAX
Solved! Go to Solution.
It's seems someone provide a solution for your requirement on your another thread, pelase check it.
number of users that change to currentstatus regular = VAR summizedTbl = SUMMARIZE ( yourTable, yourTable[customerid], "Distinct Status count", DISTINCTCOUNT ( yourTable[currentstatus] ), "status", CONCATENATEX ( yourTable, yourTable[currentstatus], "/", yourTable[reportingdate], DESC ) ) VAR rowCnt = COUNTROWS ( FILTER ( summizedTbl, [Distinct Status count] > 1 && LEFT ( [status], 7 ) = "Regular" ) ) RETURN IF ( ISBLANK ( rowCnt ), 0, rowCnt )
Its only 1 table!! i am using it against itself to work out differences
The table is like this
ID customerID Date Status
1 1212 2/3/2016 1
2 1213 2/3/2016 2
3 1212 2/4/2016 4
4 1213 2/4/2016 4
For every day, you get row for each customer
over time the status changes
What I want to know is the changes to and from status.
so i want on PowerBI a number where the number of changes to status X happens over a period of time
Yes that is correct, but what I want in the report is to show how many users have changed to status (dynamic/slicer to choose) over a certain time perid.
in the data i give,
If I were to select the last 60 days and want to know how many users moved to status 2 then the answer I want is 2!
Your previous post:
Status #of changes
Those are the right numbers - i want to get that last result - #changes is 2 where status is 4 for that time period!
@pandapanda in table visual add status, and then add status again and change the aggregation of 2nd status to distinct count
add date slicer and your table visual will filter based on the date range you select in date slicer