Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I have a table with Status column having different statuses like 'Deployed', 'End of Life', 'Ordered', etc. The source table gets updated daily along with status of existing values but their is no date column to measure when the status got modified. I am trying to build a report which gives me count of each status on a particular date by either comparing from previous run or put a date column in new table with count of each status. For Example:
Original Table:
Status Column having multiple value of statuses like 'Deployed', 'End of Life', 'Ordered',
New Table Data:
Date: 10/28/2018 (when report is run)
Count of Deployed Status: 100
Count of End of life Status: 300
Count of Ordered Status: 150
Solved! Go to Solution.
Hi @kushagravijay,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
Hi @kushagravijay,
We can create a calculated table using the formula.
Table2 = SUMMARIZE ( ADDCOLUMNS ( 'source table', "date", TODAY (), "Deployed", CALCULATE ( COUNTA ( 'source table'[Status] ), FILTER ( 'source table', 'source table'[Status] = "Deployed" ) ), "End of Life", CALCULATE ( COUNT ( 'source table'[Status] ), FILTER ( 'source table', 'source table'[Status] = "End of Life" ) ), "Ordered", CALCULATE ( COUNTA ( 'source table'[Status] ), FILTER ( 'source table', 'source table'[Status] = "End of Life" ) ) ), [date], [Deployed], [End of Life], [Ordered] )
For more details, please check the pbix as attached.
Regards,
Frank
Hi @kushagravijay,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
Hi @v-frfei-msft thanks a lot for providing the solution and your help. It worked!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |