cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sifar786 Member
Member

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 DateItem Appearance Date (Year)CHAR DESCRIPTIONCHAR VALUE
2017-02-072018LSCBBBB
2017-04-192017LSCCCCC
2017-04-192017LCCCCCC
2017-04-192017CCMBBBB
2017-04-192017CLCMBBBB
2017-06-012017LCCCCCC
2017-06-072017LSCAAAA
2017-06-072017CCMDDDD
2017-07-042018CCMGGGG
2017-07-052018CLCMAAAA
2017-07-072018LCCWWWW
2017-07-072018AVMCAAAA
2016-07-172016CLCMEEEE
2016-07-172016LSCFFFF
2016-10-112016CLCMEEEE
2016-10-122016AVMCZZZZ
2017-10-152017LSCAAAA
2017-10-182017LCCHHHH
2017-10-182017CCMBBBB
2018-03-112017CCMBBBB
2018-03-132017LCCYYYY
2018-03-132017CLCMAAAA
2018-04-122018CLCMDDDD
2018-04-132018CCMDDDD
2018-04-172018LCCCCCC
2018-04-172018AVMCCCCC
2018-06-012018LSCRRRR
2018-06-012017LCCWWWW
2018-06-012017CCMXXXX
2018-06-052017CLCMAAAA
2018-06-052017AVMCCCCC
2018-06-122017AVMCBBBB
2018-06-122017LSCHHHH

 

I am trying to answer the following questions:

  1. how many Unique Char Values are present for each Char Description each year?
  2. what are the top 5 or 10 Char Values for each Char Description each year?
  3. If we have 2 years of data, can we compare past year vs previous to determine Emerging or Trending values?
  4. Anything else interesting about this data?

 

Count of CHAR VALCHAR DESC    
Item Appearance Date (Year)CHAR VALAVMCCCMCLCMLCCLSCUniqueCharCounts
2018DDDD 11   
 BBBB    1 
 GGGG 1   1
 WWWW   1  
 RRRR    11
 AAAA1 1   
 CCCC1  1  
2018 Total 222222
2017DDDD 1    
 HHHH   111
 AAAA  2 2 
 WWWW   1  
 YYYY   1 1
 XXXX 1   1
 BBBB131   
 CCCC1  21 
2017 Total 253543
2016FFFF    11
 EEEE  2  1
 ZZZZ1    1
2016 Total 1 2 13

 

 

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Display unique items that appeared each year

@sifar786

 

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 16
Community Support Team
Community Support Team

Re: Display unique items that appeared each year

Hi @sifar786,

 

Please refer to the demo in the attachment. 

Measure =
CONCATENATEX ( VALUES ( Table1[CHAR VALUE] ), [CHAR VALUE], "-" )

Display-unique-items-that-appeared-each-year

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sifar786 Member
Member

Re: Display unique items that appeared each year

 

Thanks Dale ( @v-jiascu-msft ), Smiley Happy

 

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 DESCUniqueCharsDistinctCount
2018LSDMXXXX | HHHH2
2017LSDMXXXX | SSSS2
2016LSDMXXXX1
Grand Total XXXX | HHHH | SSSS3

 

e.g. XXXX has appeared in 2016, 2017, 2018. Hope you are getting my point.

sifar786 Member
Member

Re: Display unique items that appeared each year

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?

Community Support Team
Community Support Team

Re: Display unique items that appeared each year

Hi @sifar786,

 

Can you show up the expected result based on your data in your first post, please? I can't find out the logic here because I don't know what it should be for "LSDM", including XXXX, HHHH, SSSS. 

"They should be unique across entire daterange", why the XXXX is counted every year?

The PowerPivot in Excel and the Power BI are almost the same. You can use it directly. 

Can you modify my demo to show up what the result should be?

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sifar786 Member
Member

Re: Display unique items that appeared each year

Hi Dale ( @v-jiascu-msft ),

 

Apologies if my sample data was not clear or correct as i randomly created it. Here is a clearer example. See the Characteristic Values that i have colored using Conditional Formatting. Each appears only once during entire Daterange. These i would call "Emerging" Characteristics. The rest that are repeating every year, i would call "Trending" Characteristics.

 

Attaching the Sample Data for your inspection which has Pivots conditional formatted on Characteristic Values column.

Sample Data.xlsx

 

Also if you can please refer my Notes section for the remaining questions and guide me how they can be answered using Pivot & DAX.

 

Thanks.

Super User
Super User

Re: Display unique items that appeared each year

@sifar786

 

Give this a shot

 

DistinctCount =
IF (
    CALCULATE (
        COUNT ( tblData[CHAR VALUE] ),
        ALL ( tblData[Item Appearance Date (Year) 1], tblData[CHAR DESCRIPTION] )
    )
        = COUNT ( tblData[CHAR VALUE] ),
    1
)
Super User
Super User

Re: Display unique items that appeared each year

@sifar786

 

If you want the Grand Total as well

 

You will have to add this Additional measure as well

 

Please see your file attached

 

DistinctCount with Totals =
IF (
    HASONEFILTER ( tblData[Item Appearance Date (Year) 1] ),
    [DistinctCount],
    SUMX (
        SUMMARIZE (
            tblData,
            [Item Appearance Date (Year) 1],
            [CHAR DESCRIPTION],
            [CHAR VALUE]
        ),
        [DistinctCount]
    )
)
sifar786 Member
Member

Re: Display unique items that appeared each year

@Zubair_Muhammad getting Errors on both formulae:

 

DISTINCTCOUNT:
Semantic error: The function COUNT takes an argument that evaluates to numbers or dates and cannot work with Values of type strings.

DISTINCT COUNT WITH TOTALS:
Semantic error: Dependency error in the measure.

What could be wrong here? is it the format of the dates?

sifar786 Member
Member

Re: Display unique items that appeared each year

@Zubair_Muhammad Awesome! Man Happy

 

I replaced the COUNT with COUNTA and that worked!

 

Question: Will these measures calculate automatically if i add other filters e.g. like (Countries or Categories) or move filters (e.g. Char Desc) from Rows to Columns area in Pivot?

 

The only problem i see is that except the Measure 1, the DistinctCount & DistinctCountWithTotals measures donot appear in Sub-Totals. See below Screenshot:

 

sample.PNG

 

Also, if you can explain in brief how the 2 formulae work, it will be a good start to my learning curve.

 

Thanks.