Generate a table with unique combinations

[Ashish Mathur]

Hi,

I have a table named Dummy with two columns - Material and Status. Material has various numeric codes and Status has one of the two text values - A or I. There can be duplication combinations of material code and Status appearing. To explain better, the table looks like this

Material Status
3001 A
20003 I
3001 A
3001 I
58943 A

From this table, I would like to generate a table of unique combinations as follows

Material Status
3001 A
20003 I
3001 I
58943 A

I just wanted to confirm if the calculated field formula below would generate my desired table

=GENERATE(SUMMARIZE(Dummy,Dummy[Material]),SUMMARIZE(Dummy,Dummy[Status]))

Could you please confirm.

Thank you.

 

[Zack Barresse]

Ashish, you should be able to have a calculated column of concatenated values, then for any subsequent unique tables, use the VALUES() function to use as an intermediary/pseudo unique column. It'll probably be faster that way too. For a good example, look here:

Kasper de Jonge PowerPivot Blog » Tune your PowerPivot DAX query, don’t use the entire table in a FILTER and replace SUMX if possible
PowerPivot: DAX: Filter Functions - TechNet Articles - United States (English) - TechNet Wiki

 

[Ashish Mathur]

Thank you.