stefanycheck Tracker

Coding by color

stefanycheck — Thu, 27 Sep 2018 18:13:05 GMT

Hi! I think my problem is quite simple. I have a list of status that I want to code by color. For example:

STATUS	COLOR
STATUS1	red
STATUS2	red
STATUS3	ok
STATUS2	red
STATUS1	red
STATUS4	red

And I wanted a function like, if Table1[STATUS] is equal to STATUS1 or STATUS2 or STATUS4 and so on, equals to RED. If not, equals to OK.

Re: Start and end time of change of status

stefanycheck — Thu, 16 Aug 2018 12:39:06 GMT

Zubair_Muhammad

Hhahahaha indeed! And thans a lot for your help again, it worked just the way I wanted!!!

Start and end time of change of status

stefanycheck — Tue, 14 Aug 2018 17:34:18 GMT

Hi!

I have a table with two different status that look like this:

Date	Status	EarliestIndex	index
8/8/18 14:00	Status 1	0	0
8/8/18 14:01	Status 1	1	1
8/8/18 14:02	Status 1	1	2
8/8/18 14:03	Status 1	1	3
8/8/18 14:04	Status 1	1	4
8/8/18 14:05	Status 2	5	5
8/8/18 14:06	Status 2	5	6
8/8/18 14:07	Status 2	5	7
8/8/18 14:08	Status 1	8	8
8/8/18 14:09	Status 1	8	9
8/8/18 14:10	Status 1	8	10
8/8/18 14:11	Status 1	8	11
8/8/18 14:12	Status 2	12	12
8/8/18 14:13	Status 2	12	13
8/8/18 14:14	Status 2	12	14
8/8/18 14:15	Status 2	12	15
8/8/18 14:16	Status 1	16	16
8/8/18 14:17	Status 1	16	17
8/8/18 14:18	Status 1	16	18
8/8/18 14:19	Status 1	16	19

This EarliestIndex part is to make it so that I can count everytime a status changes.

And I want it to look like this:

Date	Status	EarliestIndex	index	Starts	Ends
8/8/18 14:00	Status 1	1	0	8/8/18 14:00	8/8/18 14:04
8/8/18 14:01	Status 1	1	1	8/8/18 14:00	8/8/18 14:04
8/8/18 14:02	Status 1	1	2	8/8/18 14:00	8/8/18 14:04
8/8/18 14:03	Status 1	1	3	8/8/18 14:00	8/8/18 14:04
8/8/18 14:04	Status 1	1	4	8/8/18 14:00	8/8/18 14:04
8/8/18 14:05	Status 2	6	5	8/8/18 14:05	8/8/18 14:07
8/8/18 14:06	Status 2	6	6	8/8/18 14:05	8/8/18 14:07
8/8/18 14:07	Status 2	6	7	8/8/18 14:05	8/8/18 14:07
8/8/18 14:08	Status 1	9	8	8/8/18 14:08	8/8/18 14:11
8/8/18 14:09	Status 1	9	9	8/8/18 14:08	8/8/18 14:11
8/8/18 14:10	Status 1	9	10	8/8/18 14:08	8/8/18 14:11
8/8/18 14:11	Status 1	9	11	8/8/18 14:08	8/8/18 14:11
8/8/18 14:12	Status 2	13	12	8/8/18 14:12	8/8/18 14:15
8/8/18 14:13	Status 2	13	13	8/8/18 14:12	8/8/18 14:15
8/8/18 14:14	Status 2	13	14	8/8/18 14:12	8/8/18 14:15
8/8/18 14:15	Status 2	13	15	8/8/18 14:12	8/8/18 14:15
8/8/18 14:16	Status 1	17	16	8/8/18 14:16	8/8/18 14:19
8/8/18 14:17	Status 1	17	17	8/8/18 14:16	8/8/18 14:19
8/8/18 14:18	Status 1	17	18	8/8/18 14:16	8/8/18 14:19
8/8/18 14:19	Status 1	17	19	8/8/18 14:16	8/8/18 14:19

So I can make some kind of report with it, showing the time each change on status began and each time it ended.

Re: Count everytime a status change

stefanycheck — Thu, 09 Aug 2018 20:57:14 GMT

Zubair_Muhammad

Actually, I could figure it out. But now, I want to make two columns, one with the date and time a status started and one with the date and time it ends. For example, now, my table looks like this:

Date	Status	EarliestIndex	index
8/8/18 14:00	Status 1	0	0
8/8/18 14:01	Status 1	1	1
8/8/18 14:02	Status 1	1	2
8/8/18 14:03	Status 1	1	3
8/8/18 14:04	Status 1	1	4
8/8/18 14:05	Status 2	5	5
8/8/18 14:06	Status 2	5	6
8/8/18 14:07	Status 2	5	7
8/8/18 14:08	Status 1	8	8
8/8/18 14:09	Status 1	8	9
8/8/18 14:10	Status 1	8	10
8/8/18 14:11	Status 1	8	11
8/8/18 14:12	Status 2	12	12
8/8/18 14:13	Status 2	12	13
8/8/18 14:14	Status 2	12	14
8/8/18 14:15	Status 2	12	15
8/8/18 14:16	Status 1	16	16
8/8/18 14:17	Status 1	16	17
8/8/18 14:18	Status 1	16	18
8/8/18 14:19	Status 1	16	19

And I want it to look like this:

Date	Status	EarliestIndex	index	Starts	Ends
8/8/18 14:00	Status 1	1	0	8/8/18 14:00	8/8/18 14:04
8/8/18 14:01	Status 1	1	1	8/8/18 14:00	8/8/18 14:04
8/8/18 14:02	Status 1	1	2	8/8/18 14:00	8/8/18 14:04
8/8/18 14:03	Status 1	1	3	8/8/18 14:00	8/8/18 14:04
8/8/18 14:04	Status 1	1	4	8/8/18 14:00	8/8/18 14:04
8/8/18 14:05	Status 2	6	5	8/8/18 14:05	8/8/18 14:07
8/8/18 14:06	Status 2	6	6	8/8/18 14:05	8/8/18 14:07
8/8/18 14:07	Status 2	6	7	8/8/18 14:05	8/8/18 14:07
8/8/18 14:08	Status 1	9	8	8/8/18 14:08	8/8/18 14:11
8/8/18 14:09	Status 1	9	9	8/8/18 14:08	8/8/18 14:11
8/8/18 14:10	Status 1	9	10	8/8/18 14:08	8/8/18 14:11
8/8/18 14:11	Status 1	9	11	8/8/18 14:08	8/8/18 14:11
8/8/18 14:12	Status 2	13	12	8/8/18 14:12	8/8/18 14:15
8/8/18 14:13	Status 2	13	13	8/8/18 14:12	8/8/18 14:15
8/8/18 14:14	Status 2	13	14	8/8/18 14:12	8/8/18 14:15
8/8/18 14:15	Status 2	13	15	8/8/18 14:12	8/8/18 14:15
8/8/18 14:16	Status 1	17	16	8/8/18 14:16	8/8/18 14:19
8/8/18 14:17	Status 1	17	17	8/8/18 14:16	8/8/18 14:19
8/8/18 14:18	Status 1	17	18	8/8/18 14:16	8/8/18 14:19
8/8/18 14:19	Status 1	17	19	8/8/18 14:16	8/8/18 14:19

So I can make some kind of report with it, showing the time each change on status began and each time it ended.

Re: Count everytime a status change

stefanycheck — Mon, 06 Aug 2018 20:55:25 GMT

Zubair_Muhammad if it isn't too much, could you also tell me how I can show this status along time? I have a column with data and time for each time this status appeared. There is a way that I can show like dots in each time with each status??

Re: Count everytime a status change

stefanycheck — Mon, 06 Aug 2018 20:51:46 GMT

It worked!!! Thanks a lot!

Re: Count everytime a status change

stefanycheck — Mon, 06 Aug 2018 18:21:21 GMT

STATUS	Index
status1	1
status1	2
status1	3
status1	4
status2	5
status2	6
status1	7
status1	8
status1	9
status1	10
status2	11
status2	12
status2	13
status1	14

Here's the sample data! I want something (a column or a measure, idk) that when I use it on the visuals, shows me that during that day, for example, I had 3 status 1 and 2 status 2, instead of 9 status 1 and 5 status 2, which would be just counting the number of times they appeared - which is exactly what I don't want it to do.

Count everytime a status change

stefanycheck — Mon, 06 Aug 2018 16:36:42 GMT

Hi!

I have this data bellow where "sentido" is the column of status. And "despejando produto"(let's call it status 1) and "mechendo produto" (let's call it status 2) are the two possible status.

I want to be able to count the number of status 1 and 2 but everytime it changes from one to another. Like, if there is status 1, status 1, status 1, status 2, status 2, status 1, status 1, I want the expression to tell that there were two status 1 and 1 status 2. I'm not sure if I'm clear enough.

I tried to make a column like this

Count = if(Consulta1[sentido]=(LOOKUPVALUE(Consulta1[sentido];Consulta1[Índice];Consulta1[Índice]-1));"";1) and them try to count the ones, but it's not working. Hope someone can help!! I can try to explain it better if you have any questions

Re: Split a Column into rows

stefanycheck — Thu, 26 Jul 2018 16:17:57 GMT

To explain it better, the whole table is in one column. So I wanted to be able to split the column so it would look like a table. For example, the table has columns like Address, Date and Price. But in the columns the names of the columns and the data are all listed in one column only. So i want to split this column in every x rows, for example.

Split a Column into rows

stefanycheck — Thu, 26 Jul 2018 15:53:43 GMT

Hi!

I imported a xml document and it should look like this:

But after importing and expanding everything it got likethe image below. So I wanted to know if there's a way to split this column to make it look like the table above! I appreciate the help!

Comparing values in the same column

stefanycheck — Wed, 25 Jul 2018 14:25:27 GMT

Hi!

I'm plotting data from MySQL to analyse data from a temperature sensor and I have a graphic showing its reading, an indicator showing the mean and cards showing the min and the max readings. Sometimes, though, the reading drops or rises too much by a transmission failure and I don't want these reading in my graphic, or in any of the other visuals. So I wanted something to filter the data when the difference between a temperature and the one before it is more than 10 degrees, for example.

In excel, it would be easy to do it, since I can compare cells, but I don't know how to do it in BI.

Here's an example of the data, the column that I want to filter is Temperatura, the other ones you can ignore hahah