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] ) )
Thanks Dale ( @v-jiascu-msft ),
I am working in Excel 2016 Powerpivot and not using Power BI. Instead of concatenating, cant we have the result as shown above, so that i can see which char values were Emerging in a particular year and which were trending from past years? Also, the length due to concatenation is exceeding in some columns and so unpleasant to read.
Also, some of these Unique Char Values seem to be present in previous years also. They should be unique across entire daterange, so that in each year we get to see which are the unique chars that have entered market.
Item Appearance Date (Year) | CHAR DESC | UniqueChars | DistinctCount |
2018 | LSDM | XXXX | HHHH | 2 |
2017 | LSDM | XXXX | SSSS | 2 |
2016 | LSDM | XXXX | 1 |
Grand Total | XXXX | HHHH | SSSS | 3 |
e.g. XXXX has appeared in 2016, 2017, 2018. Hope you are getting my point.
Hi @Anonymous,
Can you show up the expected result based on your data in your first post, please? I can't find out the logic here because I don't know what it should be for "LSDM", including XXXX, HHHH, SSSS.
"They should be unique across entire daterange", why the XXXX is counted every year?
The PowerPivot in Excel and the Power BI are almost the same. You can use it directly.
Can you modify my demo to show up what the result should be?
Best Regards,
Dale
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.
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.
@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 )
@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] ) )
@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
Did you see the attached file in previous post
Both Formulas are working in my PC.
I am using your sample file
@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 Awesome!
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 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.
Hi @Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
@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
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 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?
Hi @Zubair_Muhammad, i have seen some of your replies. Can you or any DAX Experts please help me with a solution for my problems?
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |