topic Re: DAX-Count alphabets of a Measure created by IF condition and measures ! in DAX Commands and Tips

DAX-Count alphabets of a Measure created by IF condition and measures !

souvik900766 — Thu, 21 Jan 2021 05:06:20 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 i.e. 18:-

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

Re: DAX-Count alphabets of a Measure created by IF condition and measures !

Anonymous — Thu, 21 Jan 2021 05:48:36 GMT

Hello souvik900766 ,
My measure works well.

cntrows =

CALCULATE( DISTINCTCOUNT('Count Alphabet'[Country] ), FILTER( 'Count Alphabet', 'Count Alphabet'[LCE] = "L" ) )

Re: DAX-Count alphabets of a Measure created by IF condition and measures !

souvik900766 — Thu, 21 Jan 2021 06:09:15 GMT

Anonymous
I dont know for me now its showing blank !!!

Re: DAX-Count alphabets of a Measure created by IF condition and measures !

Anonymous — Thu, 21 Jan 2021 06:22:31 GMT

Hello souvik900766 ,
Can you share your pbix file after removing sensitive data?

Re: DAX-Count alphabets of a Measure created by IF condition and measures !

Anonymous — Thu, 21 Jan 2021 08:45:53 GMT

souvik900766
Can you share your pbix file after removing the sensitive data?

Re: DAX-Count alphabets of a Measure created by IF condition and measures !

souvik900766 — Thu, 21 Jan 2021 14:13:04 GMT

Anonymous
Hi PFA link
https://www.dropbox.com/s/5w1q12q8rchf9oj/Help.pbix?dl=0

My desired answer structure:-

	L	C	E
Country Combinations	XX	XX	XX

Thanks !

Re: DAX-Count alphabets of a Measure created by IF condition and measures !

Ashish_Mathur — Fri, 22 Jan 2021 02:28:54 GMT

Hi,

This measure returns 13 as the answer which is correct

L Count 1 = COUNTROWS(FILTER(VALUES(Sheet1[ctry_nm]),[LCE]="L"))

Hope this helps.

Re: DAX-Count alphabets of a Measure created by IF condition and measures !

souvik900766 — Fri, 22 Jan 2021 06:48:27 GMT

Ashish_Mathur ,
Hi,
It worked perfectly !!! Thanks...
Just a small request !
Just pull the Volume CY measure in the table and summarize the volume by L, C, E.
My desired result and structure:-

Thanks in advance !

Re: DAX-Count alphabets of a Measure created by IF condition and measures !

souvik900766 — Fri, 22 Jan 2021 07:29:22 GMT

I have done it !

Re: DAX-Count alphabets of a Measure created by IF condition and measures !

Ashish_Mathur — Fri, 22 Jan 2021 07:36:13 GMT

You are welcome.