Forum Discussion
generate table without duplicates and count grouped values only as one element
Hello everyone,
I'm new to PowerBi and would like to summarize certain data but struggle to understand how functions such as FILTER, CALCULATETABLE work together to make this possible. Until now I was not able to solve this.
a) I need to ensure that reported successive errors that are all equal to the previous are counted only as "one error". This selection should be shown in a different table. (see different colours within source data table in column error0 and error1 as example for the needed grouping. The same colour should be counted always as "one error.
b) How can the packageID be used as first filtering element before counting the number of grouped errors to avoid that the same error within two different packageIDs is counted only once. (Example at within the given data at index 4 to 5 column error2: Following my question a) the "E20" error would only get counted as "one error". In this particular case it is speparated due to the change of the packageID. What change in the DAX / M statements would be needed?
c) create a new table that summarizes the data based on the packageID and collects the first and last time it each packageID was recorded, same for the index of the original data
=> What is the benefit of creating an additional calculated table, doing such an operation as measure or within the transformation data view?
=> Is it ensured, no matter which method from above is used, that additional filter that are applied to the dashboard may still work although they focus on other data that is given in the source table?
Source data:
| index | packageID | insert time | t_diff_prev | t_diff_succ | error0 | error1 | error2 |
| 0 | cr91234 | 01.12.2023 12:00:00.000000 | 0 | 10 | E1 | E0 | |
| 1 | cr91234 | 01.12.2023 12:00:10.000000 | 10 | 7 | E1 | E2 | E3 |
| 2 | cr91234 | 01.12.2023 12:00:17.000000 | 7 | 13 | E3 | E2 | |
| 3 | cr91234 | 01.12.2023 12:00:30.000000 | 13 | 7 | E3 | E1 | |
| 4 | cr91234 | 01.12.2023 12:00:37.000000 | 7 | 1 | E3 | E1 | E20 |
| 5 | bq4578 | 01.12.2023 12:00:38.000000 | 1 | 7 | E0 | E20 | E20 |
| 6 | bq4578 | 01.12.2023 12:00:45.000000 | 7 | 15 | E1 | E2 | E2 |
| 7 | bq4578 | 01.12.2023 12:01:00.000000 | 15 | 5 | E1 | E2 | |
| 8 | bq4578 | 01.12.2023 12:00:05.000000 | 5 | 0 | E1 | E2 |
Needed result data for a): Count occurance of grouped errors as "one error"
| error | occurance e0 | occurance e1 | occurance e2 | occurance e0+e1+e2 |
| E0 | 1 | 1 | 0 | 2 |
| E1 | 2 | 1 | 0 | 3 |
| E2 | 0 | 3 | 1 | 4 |
| E3 | 1 | 0 | 1 | 2 |
| E20 | 0 | 1 | 2 | 3 |
Needed result data for c): create table for all packageIDs withouth duplicates, Find earlist and last insert time and store each value in an individual column as well as start time and end time which result from the insert time colum of the source data column "insert time" of the first and last element that belongs to the packageID.
| packageID | start index | end index | start time | end time |
| cr91234 | 0 | 4 | 01.12.2023 12:00:00.000000 | 01.12.2023 12:00:37.000000 |
| bq4578 | 5 | 8 | 01.12.2023 12:00:38.000000 | 01.12.2023 12:00:05.000000 |
Would appreciate any help to learn and understand better how data can be filtered and how such queries work.
Cheers
Speedy
Hi Speedylux ,
In calculating the data you provided, I noticed a small discrepancy that I would like to share with you, I found that in result A, if the same color is counted 1 time, then E2 is counted 2 times in occurance e1.
Here are the steps you can follow：
1. Create calculated column.
Error0-1 = var _count=COUNTX(FILTER(ALL('Table'),'Table'[error0]=EARLIER('Table'[error0])&&'Table'[packageID]=EARLIER('Table'[packageID])),[index]) var _minindex= MINX( FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])&&'Table'[error0]=EARLIER('Table'[error0])),[index]) var _if= IF( _count=1,_count, IF( _count>1&&'Table'[index]=_minindex,1,0) ) return _ifError0-2 = SUMX( FILTER(ALL('Table'), 'Table'[error0]=EARLIER('Table'[error0])&&[error0]<>BLANK()),[Error0-1])Error1-1 = var _count=COUNTX(FILTER(ALL('Table'),'Table'[error1]=EARLIER('Table'[error1])&&'Table'[packageID]=EARLIER('Table'[packageID])),[index]) var _minindex= MINX( FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])&&'Table'[error1]=EARLIER('Table'[error1])),[index]) var _if= IF( _count=1,_count, IF( _count>1&&'Table'[index]=_minindex,1,0) ) return _ifError1-2 = SUMX( FILTER(ALL('Table'), 'Table'[error1]=EARLIER('Table'[error1])&&[error1]<>BLANK()),[Error1-1])Error2-1 = var _count=COUNTX(FILTER(ALL('Table'),'Table'[error2]=EARLIER('Table'[error2])&&'Table'[packageID]=EARLIER('Table'[packageID])),[index]) var _minindex= MINX( FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])&&'Table'[error2]=EARLIER('Table'[error2])),[index]) var _if= IF( _count=1,_count, IF( _count>1&&'Table'[index]=_minindex,1,0) ) return _ifError2-2 = SUMX( FILTER(ALL('Table'), 'Table'[error2]=EARLIER('Table'[error2])&&[error2]<>BLANK()),[Error2-1])2. Enter data – create a table.
Create calculated column
occurance e0 = var _value= MAXX( FILTER(ALL('Table'), 'Table'[error0]=EARLIER('result table'[error])),[Error0-2]) return IF( _value=BLANK(),0,_value)occurance e1 = var _value= MAXX( FILTER(ALL('Table'), 'Table'[error1]=EARLIER('result table'[error])),[Error1-2]) return IF( _value=BLANK(),0,_value)occurance e2 = var _value= MAXX( FILTER(ALL('Table'), 'Table'[error2]=EARLIER('result table'[error])),[Error2-2]) return IF( _value=BLANK(),0,_value)occurance e0+e1+e2 = [occurance e0] + [occurance e1] + [occurance e2]3. Create calculated table.
Table 2 = SUMMARIZE( 'Table','Table'[packageID], "start index",MINX(FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])),[index]), "end index",MAXX(FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])),[index]), "start time",MINX(FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])),[insert time]), "end time",MAXX(FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])),[insert time]))4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi Speedylux ,
In calculating the data you provided, I noticed a small discrepancy that I would like to share with you, I found that in result A, if the same color is counted 1 time, then E2 is counted 2 times in occurance e1.
Here are the steps you can follow：
1. Create calculated column.
Error0-1 = var _count=COUNTX(FILTER(ALL('Table'),'Table'[error0]=EARLIER('Table'[error0])&&'Table'[packageID]=EARLIER('Table'[packageID])),[index]) var _minindex= MINX( FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])&&'Table'[error0]=EARLIER('Table'[error0])),[index]) var _if= IF( _count=1,_count, IF( _count>1&&'Table'[index]=_minindex,1,0) ) return _ifError0-2 = SUMX( FILTER(ALL('Table'), 'Table'[error0]=EARLIER('Table'[error0])&&[error0]<>BLANK()),[Error0-1])Error1-1 = var _count=COUNTX(FILTER(ALL('Table'),'Table'[error1]=EARLIER('Table'[error1])&&'Table'[packageID]=EARLIER('Table'[packageID])),[index]) var _minindex= MINX( FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])&&'Table'[error1]=EARLIER('Table'[error1])),[index]) var _if= IF( _count=1,_count, IF( _count>1&&'Table'[index]=_minindex,1,0) ) return _ifError1-2 = SUMX( FILTER(ALL('Table'), 'Table'[error1]=EARLIER('Table'[error1])&&[error1]<>BLANK()),[Error1-1])Error2-1 = var _count=COUNTX(FILTER(ALL('Table'),'Table'[error2]=EARLIER('Table'[error2])&&'Table'[packageID]=EARLIER('Table'[packageID])),[index]) var _minindex= MINX( FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])&&'Table'[error2]=EARLIER('Table'[error2])),[index]) var _if= IF( _count=1,_count, IF( _count>1&&'Table'[index]=_minindex,1,0) ) return _ifError2-2 = SUMX( FILTER(ALL('Table'), 'Table'[error2]=EARLIER('Table'[error2])&&[error2]<>BLANK()),[Error2-1])2. Enter data – create a table.
Create calculated column
occurance e0 = var _value= MAXX( FILTER(ALL('Table'), 'Table'[error0]=EARLIER('result table'[error])),[Error0-2]) return IF( _value=BLANK(),0,_value)occurance e1 = var _value= MAXX( FILTER(ALL('Table'), 'Table'[error1]=EARLIER('result table'[error])),[Error1-2]) return IF( _value=BLANK(),0,_value)occurance e2 = var _value= MAXX( FILTER(ALL('Table'), 'Table'[error2]=EARLIER('result table'[error])),[Error2-2]) return IF( _value=BLANK(),0,_value)occurance e0+e1+e2 = [occurance e0] + [occurance e1] + [occurance e2]3. Create calculated table.
Table 2 = SUMMARIZE( 'Table','Table'[packageID], "start index",MINX(FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])),[index]), "end index",MAXX(FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])),[index]), "start time",MINX(FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])),[insert time]), "end time",MAXX(FILTER(ALL('Table'),'Table'[packageID]=EARLIER('Table'[packageID])),[insert time]))4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly