Forum Discussion
Display unique items that appeared each year
I am new to DAX and using Power Query & Powerpivot in Excel 2016 to transform and load data to a pivot. I am simply trying to get a list of Unique Characteristics Values (CHAR VAL) and their counts (Sub-Totals) shown for each year. This measure should adjust itself when i add different filters like Country, Char Description etc to Pivot.
| Item Appearance Date | Item Appearance Date (Year) | CHAR DESCRIPTION | CHAR VALUE |
| 2017-02-07 | 2018 | LSC | BBBB |
| 2017-04-19 | 2017 | LSC | CCCC |
| 2017-04-19 | 2017 | LCC | CCCC |
| 2017-04-19 | 2017 | CCM | BBBB |
| 2017-04-19 | 2017 | CLCM | BBBB |
| 2017-06-01 | 2017 | LCC | CCCC |
| 2017-06-07 | 2017 | LSC | AAAA |
| 2017-06-07 | 2017 | CCM | DDDD |
| 2017-07-04 | 2018 | CCM | GGGG |
| 2017-07-05 | 2018 | CLCM | AAAA |
| 2017-07-07 | 2018 | LCC | WWWW |
| 2017-07-07 | 2018 | AVMC | AAAA |
| 2016-07-17 | 2016 | CLCM | EEEE |
| 2016-07-17 | 2016 | LSC | FFFF |
| 2016-10-11 | 2016 | CLCM | EEEE |
| 2016-10-12 | 2016 | AVMC | ZZZZ |
| 2017-10-15 | 2017 | LSC | AAAA |
| 2017-10-18 | 2017 | LCC | HHHH |
| 2017-10-18 | 2017 | CCM | BBBB |
| 2018-03-11 | 2017 | CCM | BBBB |
| 2018-03-13 | 2017 | LCC | YYYY |
| 2018-03-13 | 2017 | CLCM | AAAA |
| 2018-04-12 | 2018 | CLCM | DDDD |
| 2018-04-13 | 2018 | CCM | DDDD |
| 2018-04-17 | 2018 | LCC | CCCC |
| 2018-04-17 | 2018 | AVMC | CCCC |
| 2018-06-01 | 2018 | LSC | RRRR |
| 2018-06-01 | 2017 | LCC | WWWW |
| 2018-06-01 | 2017 | CCM | XXXX |
| 2018-06-05 | 2017 | CLCM | AAAA |
| 2018-06-05 | 2017 | AVMC | CCCC |
| 2018-06-12 | 2017 | AVMC | BBBB |
| 2018-06-12 | 2017 | LSC | HHHH |
I am trying to answer the following questions:
- how many Unique Char Values are present for each Char Description each year?
- what are the top 5 or 10 Char Values for each Char Description each year?
- If we have 2 years of data, can we compare past year vs previous to determine Emerging or Trending values?
- Anything else interesting about this data?
| Count of CHAR VAL | CHAR DESC | ||||||
| Item Appearance Date (Year) | CHAR VAL | AVMC | CCM | CLCM | LCC | LSC | UniqueCharCounts |
| 2018 | DDDD | 1 | 1 | ||||
| BBBB | 1 | ||||||
| GGGG | 1 | 1 | |||||
| WWWW | 1 | ||||||
| RRRR | 1 | 1 | |||||
| AAAA | 1 | 1 | |||||
| CCCC | 1 | 1 | |||||
| 2018 Total | 2 | 2 | 2 | 2 | 2 | 2 | |
| 2017 | DDDD | 1 | |||||
| HHHH | 1 | 1 | 1 | ||||
| AAAA | 2 | 2 | |||||
| WWWW | 1 | ||||||
| YYYY | 1 | 1 | |||||
| XXXX | 1 | 1 | |||||
| BBBB | 1 | 3 | 1 | ||||
| CCCC | 1 | 2 | 1 | ||||
| 2017 Total | 2 | 5 | 3 | 5 | 4 | 3 | |
| 2016 | FFFF | 1 | 1 | ||||
| EEEE | 2 | 1 | |||||
| ZZZZ | 1 | 1 | |||||
| 2016 Total | 1 | 2 | 1 | 3 |
I have tried following formulae, but not getting the unique list of items and their unique counts in pivot for each year. e.g. like this:
DistinctCounts:=DISTINCTCOUNT('Table1'[CHAR VAL]))
UniqueCharCount:=CALCULATE(COUNTROWS(DISTINCT('Table1'[CHAR VAL])),'Table1'[Item Appearance Date (Year)])
Can any DAX Experts please help me quickly?
Anonymous
Try this revision
Distinct Count With Totals = IF ( HASONEFILTER ( tblData[Item Appearance Date (Year) 1] ) && HASONEFILTER ( tblData[CHAR DESCRIPTION] ) && HASONEFILTER ( [CHAR VALUE] ), [DistinctCount], SUMX ( SUMMARIZE ( tblData, [Item Appearance Date (Year) 1], [CHAR DESCRIPTION], [CHAR VALUE] ), [DistinctCount] ) )
16 Replies
v-jiascu-msftMicrosoft Employee
Hi Anonymous,
Please refer to the demo in the attachment.
Measure = CONCATENATEX ( VALUES ( Table1[CHAR VALUE] ), [CHAR VALUE], "-" )
Best Regards,
DaleThanks Dale ( v-jiascu-msft ), :smileyhappy:
I am working in Excel 2016 Powerpivot and not using Power BI. Instead of concatenating, cant we have the result as shown above, so that i can see which char values were Emerging in a particular year and which were trending from past years? Also, the length due to concatenation is exceeding in some columns and so unpleasant to read.
Also, some of these Unique Char Values seem to be present in previous years also. They should be unique across entire daterange, so that in each year we get to see which are the unique chars that have entered market.
Item Appearance Date (Year) CHAR DESC UniqueChars DistinctCount 2018 LSDM XXXX | HHHH 2 2017 LSDM XXXX | SSSS 2 2016 LSDM XXXX 1 Grand Total XXXX | HHHH | SSSS 3 e.g. XXXX has appeared in 2016, 2017, 2018. Hope you are getting my point.
Hi Zubair_Muhammad, i have seen some of your replies. Can you or any DAX Experts please help me with a solution for my problems?