topic Re: Count Alphabets in if Condition by measure. in DAX Commands and Tips

Count Alphabets in if Condition by measure.

souvik900766 — Wed, 20 Jan 2021 16:34:32 GMT

Hi ! I want to count or summarize the alphabets created from the if statement by a measure !
My Data:-
My measure of the if statement:-

LCE _1 =

IF([M]>=1,"",IF(Share(Val)]>0.30 && [M]>=0.01,"L",

IF( [Share(Val)]>0.10 && [M]>=0.01,"C",

IF([Share(Val)]<0.10 || [M]<=0.01,"E"))))
Both [M] & [Share(Val)] are measures !

Country	Category	LCE	Volume	Value
Country 1	Category 1	C	0	3
Country 2	Category 1	L	0	4
Country 3	Category 1	C	0	6
Country 4	Category 1	C	1	15
Country 5	Category 1	C	1	15
Country 6	Category 1	C	0	0
Country 7	Category 1	L	0	2
Country 8	Category 1	L	0	4
Country 9	Category 1	C	0	0
Country 10	Category 1	E	0	2
Country 11	Category 1	C	0	3
Country 12	Category 1	C	0	1
Country 13	Category 1	C	1	14
Country 14	Category 1	L	0	4
Country 15	Category 1	L	0	6
Country 16	Category 1	C	0	1
Country 17	Category 1	L	7	143
Country 18	Category 1	E	0	0

The LCE column is a measure created with IF statement:-

I want to count or summarize , that number of countries coming under L, C, E and also the summation of Volume and Value under L,C,E.
My desired answer and structure:-

	L	C	E
Country Combinations	6	10	2
Volume	7.8	3.6	0.1
Value	164	57	1.7

I used this measue but it is giving the total count of LCE:-

cntrows = CALCULATE(DISTINCTCOUNT(country),FILTER(Table,[LCE]="L"))

Thanks in advance !

Re: Count Alphabets in if Condition by measure.

Anonymous — Wed, 20 Jan 2021 09:10:15 GMT

Hello souvik900766
You can create a calculated table by using the below calculation.
CountAlphabet =
UNION (
SUMMARIZE (
'Count Alphabet',
"Values", "Country Combinations",
"L", CALCULATE ( COUNT ( 'Count Alphabet'[LCE] ), 'Count Alphabet'[LCE] = "L" ),
"C", CALCULATE ( COUNT ( 'Count Alphabet'[LCE] ), 'Count Alphabet'[LCE] = "C" ),
"E", CALCULATE ( COUNT ( 'Count Alphabet'[LCE] ), 'Count Alphabet'[LCE] = "E" )
),
SUMMARIZE (
'Count Alphabet',
"Values", "Volume",
"L", CALCULATE ( SUM ( 'Count Alphabet'[Volume] ), 'Count Alphabet'[LCE] = "L" ),
"C", CALCULATE ( SUM ( 'Count Alphabet'[Volume] ), 'Count Alphabet'[LCE] = "C" ),
"E", CALCULATE ( SUM ( 'Count Alphabet'[Volume] ), 'Count Alphabet'[LCE] = "E" )
),
SUMMARIZE (
'Count Alphabet',
"Values", "Value",
"L", CALCULATE ( SUM ( 'Count Alphabet'[Value] ), 'Count Alphabet'[LCE] = "L" ),
"C", CALCULATE ( SUM ( 'Count Alphabet'[Value] ), 'Count Alphabet'[LCE] = "C" ),
"E", CALCULATE ( SUM ( 'Count Alphabet'[Value] ), 'Count Alphabet'[LCE] = "E" )
)
)

Re: Count Alphabets in if Condition by measure.

souvik900766 — Wed, 20 Jan 2021 14:14:42 GMT

Hi !
My LCE column that you see is based out of a LOGICAL IF Measure with certain condition.Then how can I use count?