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

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

    Zubair_Muhammad This gives sub-totals now for only the Year column, and not for the Char Desc or Char Value columns. I think if i add more criteria in the Rows or Columns area of Pivot, then Sub-Totals will not appear for those new criteria, isn't it?

     

     

     

    Can this Totals measure be made more Generic so that it calculates unique values and their subtotals and totals correctly, when data is sliced/diced?

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Zubair_Muhammad i checked the measures with my original data and works fine but Sub-Totals are not showing up, so i cannot Chart this data as Chart shows all 1's.

     

    Also, this works only if i add Year, Char Desc & Char Val to Rows section in Pivot. Country and Category filters are not present in measure.

     

    Need your urgent help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Zubair_Muhammad i checked the measures with my original data and works fine but SubTotals are not showing up, so i cannot Chart this data as Chart shows all 1's.

     

    Also, this works only if i add Year, Char Desc & Char Val to Rows section in Pivot. Country and Category filters are not present in measure.

     

    Need your urgent help.

     

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

    Hi Anonymous,

     

    Could you please mark the proper answers as solutions?

     

    Best Regards,

    Dale