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

 

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.