Forum Discussion

Helper I

6 years ago

Solved

Power 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 dat...

Need Help

Anonymous
6 years ago
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.

-Ozym4nd1az-

Helper I

6 years ago

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

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

Anonymous

Not applicable

6 years ago

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.

-Ozym4nd1az-
Helper I
6 years ago
Hi Anonymous ,

If I use your first solution with visual filter can I still perform calculations which it is going to consider the filter level for that?

I need to performed some calculations with that column 'SLA not MET'.
- Anonymous
  Not applicable
  6 years ago
  Hi -Ozym4nd1az- ,
  
  It's a visual level filter and only calculation on the visual will be affected.
  
  Or you can use ALL() function to remove the filter in your formula if you need.
  
  Generally, it depends on your need and your dax formula.
  
  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-
Helper I
6 years ago
Hi Anonymous ,

I have two problems with your measure method: 1) it is changing the date of some items 2) it is not excluding the item.
I did as you have mentioned
- -Ozym4nd1az-
  Helper I
  6 years ago
  Anonymous method's using new table worked, so I abandoned that approach.