Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have this data
How can I show a report with the number of users that change to currentstatus =regular from any previous status?
customerid | reportingdate | totalorders | currentstatus | totalamountordered |
1 | 8-Nov-17 | 2 | Irregular | 15.65 |
2 | 8-Nov-17 | 3 | Irregular | 34.1 |
3 | 8-Nov-17 | 6 | Regular | 45 |
1 | 9-Nov-17 | 2 | Irregular | 15.65 |
2 | 9-Nov-17 | 3 | Irregular | 34.1 |
3 | 9-Nov-17 | 6 | Sleeper | 45 |
1 | 10-Nov-17 | 3 | Irregular | 20 |
2 | 10-Nov-17 | 3 | Irregular | 34.1 |
3 | 10-Nov-17 | 6 | Sleeper | 45 |
1 | 11-Nov-17 | 3 | Irregular | 20 |
2 | 11-Nov-17 | 4 | Regular | 50 |
3 | 11-Nov-17 | 7 | Irregular | 55 |
1 | 12-Nov-17 | 4 | Regular | 30 |
2 | 12-Nov-17 | 4 | Regular | 50 |
3 | 12-Nov-17 | 7 | Irregular | 55 |
Expected result if date start is 8th nov and end date is 12th November is 2 | ||||
Expected result if date start is 8th nov and end date is 10th November is 0 | ||||
Expected result if date start is 11th nov and end date is 12th November is 1 |
Solved! Go to Solution.
@pandapanda wrote:
I have this data
How can I show a report with the number of users that change to currentstatus =regular from any previous status?
customerid reportingdate totalorders currentstatus totalamountordered 1 8-Nov-17 2 Irregular 15.65 2 8-Nov-17 3 Irregular 34.1 3 8-Nov-17 6 Regular 45 1 9-Nov-17 2 Irregular 15.65 2 9-Nov-17 3 Irregular 34.1 3 9-Nov-17 6 Sleeper 45 1 10-Nov-17 3 Irregular 20 2 10-Nov-17 3 Irregular 34.1 3 10-Nov-17 6 Sleeper 45 1 11-Nov-17 3 Irregular 20 2 11-Nov-17 4 Regular 50 3 11-Nov-17 7 Irregular 55 1 12-Nov-17 4 Regular 30 2 12-Nov-17 4 Regular 50 3 12-Nov-17 7 Irregular 55 Expected result if date start is 8th nov and end date is 12th November is 2 Expected result if date start is 8th nov and end date is 10th November is 0 Expected result if date start is 11th nov and end date is 12th November is 1
You can try a measure as below. See more in the attached pbix file.
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 )
@pandapanda wrote:
I have this data
How can I show a report with the number of users that change to currentstatus =regular from any previous status?
customerid reportingdate totalorders currentstatus totalamountordered 1 8-Nov-17 2 Irregular 15.65 2 8-Nov-17 3 Irregular 34.1 3 8-Nov-17 6 Regular 45 1 9-Nov-17 2 Irregular 15.65 2 9-Nov-17 3 Irregular 34.1 3 9-Nov-17 6 Sleeper 45 1 10-Nov-17 3 Irregular 20 2 10-Nov-17 3 Irregular 34.1 3 10-Nov-17 6 Sleeper 45 1 11-Nov-17 3 Irregular 20 2 11-Nov-17 4 Regular 50 3 11-Nov-17 7 Irregular 55 1 12-Nov-17 4 Regular 30 2 12-Nov-17 4 Regular 50 3 12-Nov-17 7 Irregular 55 Expected result if date start is 8th nov and end date is 12th November is 2 Expected result if date start is 8th nov and end date is 10th November is 0 Expected result if date start is 11th nov and end date is 12th November is 1
You can try a measure as below. See more in the attached pbix file.
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 )
Hi,
I have a small clarification to seek. When you say that the date range is 8/Nov to 12/Nov, then do you only want to compare the status on only the opening and closing dates or see the ones in the intervening period as well? So for example, if the status of ID3 was regular on 12/Nov, then should that id be excluded when studying the dates range 8/Nov to 12/Nov (he stayed as regular on both those dates) or should he be included because his status changed from Irregular on 11/Nov to regular on 12/Nov.
Please clarify.
Hi @pandapanda
Hope it helps
Following MEASURE is used
My_Count = COUNTROWS ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( TableName, TableName[customerid], "Status Start", CALCULATE ( VALUES ( TableName[currentstatus] ), FILTER ( TableName, TableName[reportingdate] = MIN ( TableName[reportingdate] ) ) ), "Status End", CALCULATE ( VALUES ( TableName[currentstatus] ), FILTER ( TableName, TableName[reportingdate] = MAX ( TableName[reportingdate] ) ) ) ), "Change in Status", IF ( [Status End] = "Regular" && [Status Start] <> "Regular", "Yes", "No" ) ), [Change in Status] = "Yes" ) )
If you do not have millions of rows, something along these lines might work:
StatusChanged = var nextStatus = MINX(FILTER(table,table[customerid]=EARLIER(table[customerid])&&table[currentstatus]<>EARLIER(table[currentstatus])&&table[reportingdate]>EARLIER(table[reportingdate])),table[currentstatus]) RETURN IF(nextStatus <> [currentstatus],1,0)
You would then have a column that you could count.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |