topic How to create measure of the countrow values at variable table ? in DAX Commands and Tips

How to create measure of the countrow values at variable table ?

ChoiJunghoon — Fri, 10 Dec 2021 02:03:43 GMT

Hello. I have a big problem.

Table1

ModuleID	\| StatusScore	\| Sequence	\| Loction
A1	0	1	A
A1	0	2	B
A1	1	3	C
A2	0	5	D
A2	0	3	E
A2	0	4	F
A3	1	1	A
A3	10	2	B
A3	1	3	C
A4	0	4	D
A4	20	5	E
A4	0	6	F
A5	0	7	A
A5	0	8	B
A5	0	9	C

Table2

ModuleID

Table3 ** Relationship (Table1[Location)

Location

-----------------------------------------------------------

create Measure value

Measure = VAR x0 = SUMMARIZE ( FILTER ( Table1, NOT ( Table1[ModuleID] IN VALUES ( 'Table2'[ModuleID] ) ) ), Table1[ModuleID], "SUM_", SUM ( Table1[StatusScore] ), "LastSequence", MAX ( Table1[Sequence] ), "location",CALCULATE(max(Table1[Loction]),Table1[Sequence] = MAX ( Table1[Sequence] )) ) VAR x1= FILTER(x0,[SUM_]=0) RETURN COUNTROWS(x1)

and I add the table in the report. (Display)

Location	Measure
A	1
B	1
C	1
D	2
E	1
F	2

but , I want to display below table by using (x1) table.

Location	Measure
C	1
D	1

Could you let me know what is wrong ?

Re: How to create measure of the countrow values at variable table ?

VahidDM — Fri, 10 Dec 2021 03:20:06 GMT

Hi ChoiJunghoon

It because of the relation between those table1 and table3,remove that relation and test it again.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: How to create measure of the countrow values at variable table ?

ChoiJunghoon — Fri, 10 Dec 2021 04:19:42 GMT

sorry, After remove relation.

Location	Measure
A	2
B	2
C	2
D	2
E	2
F	2

Re: How to create measure of the countrow values at variable table ?

VahidDM — Sat, 11 Dec 2021 05:24:56 GMT

try this:

Measure = VAR x0 = SUMMARIZE( FILTER( Table1, NOT ( Table1[ModuleID] IN VALUES( 'Table2'[ModuleID] ) ) ), Table1[ModuleID], "SUM_", SUM( Table1[StatusScore] ), "LastSequence", MAX( Table1[Sequence] ), "location", CALCULATE( MAX( Table1[Loction] ), FILTER( Table1, Table1[Sequence] = MAX( Table1[Sequence] ) ) ) ) VAR x1 = FILTER( x0, [SUM_] = 0 ) RETURN COUNTROWS( x1 )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/