Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 (Sub-Totals) shown for each year. This measure should adjust itself when i add different filters like Country, Char Description etc to Pivot.
Item Appearance Date | Item Appearance Date (Year) | CHAR DESCRIPTION | CHAR VALUE |
2017-02-07 | 2018 | LSC | BBBB |
2017-04-19 | 2017 | LSC | CCCC |
2017-04-19 | 2017 | LCC | CCCC |
2017-04-19 | 2017 | CCM | BBBB |
2017-04-19 | 2017 | CLCM | BBBB |
2017-06-01 | 2017 | LCC | CCCC |
2017-06-07 | 2017 | LSC | AAAA |
2017-06-07 | 2017 | CCM | DDDD |
2017-07-04 | 2018 | CCM | GGGG |
2017-07-05 | 2018 | CLCM | AAAA |
2017-07-07 | 2018 | LCC | WWWW |
2017-07-07 | 2018 | AVMC | AAAA |
2016-07-17 | 2016 | CLCM | EEEE |
2016-07-17 | 2016 | LSC | FFFF |
2016-10-11 | 2016 | CLCM | EEEE |
2016-10-12 | 2016 | AVMC | ZZZZ |
2017-10-15 | 2017 | LSC | AAAA |
2017-10-18 | 2017 | LCC | HHHH |
2017-10-18 | 2017 | CCM | BBBB |
2018-03-11 | 2017 | CCM | BBBB |
2018-03-13 | 2017 | LCC | YYYY |
2018-03-13 | 2017 | CLCM | AAAA |
2018-04-12 | 2018 | CLCM | DDDD |
2018-04-13 | 2018 | CCM | DDDD |
2018-04-17 | 2018 | LCC | CCCC |
2018-04-17 | 2018 | AVMC | CCCC |
2018-06-01 | 2018 | LSC | RRRR |
2018-06-01 | 2017 | LCC | WWWW |
2018-06-01 | 2017 | CCM | XXXX |
2018-06-05 | 2017 | CLCM | AAAA |
2018-06-05 | 2017 | AVMC | CCCC |
2018-06-12 | 2017 | AVMC | BBBB |
2018-06-12 | 2017 | LSC | HHHH |
I am trying to answer the following questions:
Count of CHAR VAL | CHAR DESC | ||||||
Item Appearance Date (Year) | CHAR VAL | AVMC | CCM | CLCM | LCC | LSC | UniqueCharCounts |
2018 | DDDD | 1 | 1 | ||||
BBBB | 1 | ||||||
GGGG | 1 | 1 | |||||
WWWW | 1 | ||||||
RRRR | 1 | 1 | |||||
AAAA | 1 | 1 | |||||
CCCC | 1 | 1 | |||||
2018 Total | 2 | 2 | 2 | 2 | 2 | 2 | |
2017 | DDDD | 1 | |||||
HHHH | 1 | 1 | 1 | ||||
AAAA | 2 | 2 | |||||
WWWW | 1 | ||||||
YYYY | 1 | 1 | |||||
XXXX | 1 | 1 | |||||
BBBB | 1 | 3 | 1 | ||||
CCCC | 1 | 2 | 1 | ||||
2017 Total | 2 | 5 | 3 | 5 | 4 | 3 | |
2016 | FFFF | 1 | 1 | ||||
EEEE | 2 | 1 | |||||
ZZZZ | 1 | 1 | |||||
2016 Total | 1 | 2 | 1 | 3 |
I have tried following formulae, but not getting the unique list of items and their unique counts in pivot for each year. e.g. like this:
DistinctCounts:=DISTINCTCOUNT('Table1'[CHAR VAL]))
UniqueCharCount:=CALCULATE(COUNTROWS(DISTINCT('Table1'[CHAR VAL])),'Table1'[Item Appearance Date (Year)])
Can any DAX Experts please help me quickly?
Solved! Go to Solution.
@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] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |