Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 (...
Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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]
    )
)

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

 

Thanks 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 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.

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Anonymous

       

      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
)
      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Anonymous

         

        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]
    )
)