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

     

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.

       

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

        Anonymous

         

        To get the Subtotals i.e for each year

         

        use this formula

         

        Distinct Count With Totals =
IF (
    HASONEFILTER ( tblData[Item Appearance Date (Year) 1] )
        && HASONEFILTER ( tblData[CHAR DESCRIPTION] ),
    [DistinctCount],
    SUMX (
        SUMMARIZE (
            tblData,
            [Item Appearance Date (Year) 1],
            [CHAR DESCRIPTION],
            [CHAR VALUE]
        ),
        [DistinctCount]
    )
)
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Anonymous

       

      Did you see the attached file in previous post

       

      Both Formulas are working in my PC.

      I am using your sample file

       

       

       

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