Forum Discussion
Helper IPower BI distinct values for column for table creation
I am trying to perform a deletion of a row that is duplicated in Powerbi visuals. I cannot use Power Query here. So, I must delete the row with duplicated values for the columns values of Monthly date and Number.
I want to keep just the values which have 'SLA met' column 1 and remove the one which has 1 for 'SLA not met'
'INC753151' could be an example.
I would like just to let the row of that INC which has value for 'SLA met' equal 1, as mentioned. Then, after use the table normally.
Any suggestion?
Hi -Ozym4nd1az- ,
I see. So you should use DISTINCT() function to create a distinct table and use this table to create a visual.
Then create a measure as below and add it to visual level filter.
Measure = IF(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[number]))=1,1,IF(SELECTEDVALUE('Table'[SLA not met])=1,0,1))Or if you want a table, you can create a calculated column on the raw table as below.
Column = IF(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[number]))=1,1,IF('Table'[SLA not met]=1,0,1))Then do the distinct work.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
- amitchandak
Super User
-Ozym4nd1az- , You have to take min /max/count/sum etc. If need firstnonblank or lastnonblank
- parry2k
-Ozym4nd1az- there are many ways to handle it, one is simpy create a measure with the following expression and use that in your visuals, remove SLA met and SLA not met columns from the visual
SLA Met Measure = CALCULATE ( COUNTROWS( Table ), Table[SLA Met] = 1 ) )or add a page or visual level filter with a condition where SLA Met = 1
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Hi -Ozym4nd1az- ,
Are you want this?
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
-Ozym4nd1az-Yes, Anonymous . However, I need to consider one of the column with 'SLA not MET' as well. For example, in your examples we have two number ones in the column 'SLA not MET' it is not going to be possible, always we will have at maximum one number one there.
The case is that I need to show the column 'SLA not MET' with its numbers, when there are no duplicated values for one 'Number' there. If there are duplicated values, I should deleted the row with 'SLA not MET' = 1 and keep the rest of the values in the column.
A table like below would result in the second table after the measures applied (or DAX in the calculate table):
date | number | SLA met | SLA not met
1/1/2020 1 1
1/1/2020 1 1
2/1/2020 2 1
3/1/2020 4 1
Result:
date | number | SLA met | SLA not met
1/1/2020 1 1
2/1/2020 2 1
3/1/2020 4 1
Hi -Ozym4nd1az- ,
I see. So you should use DISTINCT() function to create a distinct table and use this table to create a visual.
Then create a measure as below and add it to visual level filter.
Measure = IF(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[number]))=1,1,IF(SELECTEDVALUE('Table'[SLA not met])=1,0,1))Or if you want a table, you can create a calculated column on the raw table as below.
Column = IF(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[number]))=1,1,IF('Table'[SLA not met]=1,0,1))Then do the distinct work.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello -Ozym4nd1az- I understand that you need to do a group by by date and number of records that complied with the SLA, it can work something like this:
NewTable = SUMMARIZE ( ALL ( Table[Monthly Date] ); Table[Monthly Date]; "Count"; SUM ( Table[SLA met] ) )In essence, that logic can go on.
