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

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]
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Zubair_Muhammad Awesome! :manhappy:

         

        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:

         

         

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

         

        Thanks.