How to get the measure value by using DAX

[ChoiJunghoon]

Hello. SuperMan.
Would you like to help me to get measure value? 

Data connection : Direct Query

I want to know location Status.

Table_Location

LocationID

A1

A2

A3

B1

B2

B3

C1

C2

C3

Table_LogHistory

TestID	Status	LocationID	Sequence
M1	Ongoing	A1	1
M1	Paused	A2	2
M1	Done	C3	3
M2	Done	B1	1
M2	Done	B2	2
M2	Done	B3	3
M3	Ongoing	C1	1
M3	Ongoing	C2	2
M4	Done	A1	1
M4	Done	B1	2
M4	Ongoing	C1	3
M4	Ongoing	C2	4

Measure 

Step 1. Var table. 

Ongoing&Paused 

LocationID	TestID	TestID Count
A1	M1	1
A2	M1	1
A3
B1
B2
B3
C1	M3,C1	2
C2	M3	1
C3

Step2. Done 

TestID	Status	MaxSequece
M1	Ongoing,Paused	3
M2	Done	3
M3	Ongoing	2
M4	Done,Ongoing	4

Step3. Done 

TestID	Status	MaxSequece	LocationID
M2	Done	3	B3

Step. 

Ongoing + Paused + Done

Measure : Test ID Value

Measrue : Test ID Count 

Location	TestID	TestID Count
A1	M1	1
A2	M1	1
A3
B1
B2
B3	M2	1
C1	M3,C1	2
C2	M3	1
C3

Could you help to create the Measure ?? 

[Anonymous]

Hi @ChoiJunghoon ,

I'm confused about your requirements.

Is the screenshot below the desired result you want? Why is there C1 in C1? In Step2, the title is Done, but there are Ongoing and Paused in the Status column.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @ChoiJunghoon 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/