cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maheshrakwal Frequent Visitor
Frequent Visitor

Dax Calculation to remove similar records.

Below DAX gives some output. and i want to eliminate those records where i am getting same data for all the records.

 

Remove Highlighted Records.PNGData is same for all the records here.

 

DAX : 

 

List of Part Details values =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('configuration_analysis'[Part Details])
VAR __MAX_VALUES_TO_SHOW = 6
RETURN
IF(
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
CONCATENATE(
CONCATENATEX(
TOPN(
__MAX_VALUES_TO_SHOW,
VALUES('configuration_analysis'[Part Details]),
'configuration_analysis'[Part Details],
ASC
),
'configuration_analysis'[Part Details],
", ",
'configuration_analysis'[Part Details],
ASC
),
", etc."
),
CONCATENATEX(
VALUES('configuration_analysis'[Part Details]),
'configuration_analysis'[Part Details],
", ",
'configuration_analysis'[Part Details],
ASC
)
)

3 REPLIES 3
taisiya Regular Visitor
Regular Visitor

Re: Dax Calculation to remove similar records.

Hi,

 

This might help you https://www.c-sharpcorner.com/article/variable-in-dax-keep-rows-and-remove-rows-in-power-bi/

 

OR 

 

Create a Custom column. Use SWITCH function to find rows where all columns contain the same variable:

 

Column = SWITCH(TRUE(),
column_name = column_name1, "1", 

column_name1 = column_name2, "1",

column_name3 = column_name4, "1", 

"0"

 

And then simply filter out the calculated column by selecting only those values with the value "0".

 

Hope it helps.

 

maheshrakwal Frequent Visitor
Frequent Visitor

Re: Dax Calculation to remove similar records.

Sorry i forget to mention. above table (image) is matrix and from 3 column onwards all are values of attribute DCA (attribute_name).

Community Support Team
Community Support Team

Re: Dax Calculation to remove similar records.

Hi @maheshrakwal

Is the DAX "List of Part Details values" a column or a measure?

I can't find it in the matrix, could you tell me where does this formula add?

Which cells do you need to eliminate? Is the bright yellow line?

 

Additionally, you could consider SUMMARIZE function to remove the duplicated rows/values.

Similar thread

https://community.powerbi.com/t5/Desktop/Power-BI-DAX-query-to-remove-duplicated-rows-values-in-SUM/...

 

Could you share me some data as example so i can analyze in your scenario?

 

Best Regards

Maggie