Making a filtering Measure to show the count in a card

[Kostas]

Hello, 

I am currently working in a project and having a specific issue. 
I am having a table as shows below:

ID 1	Status ID1	ID 2	Status ID2
1233	Complete	3213	Complete
2131	Pending	4351	Pending
5112	In Progress	5236	Pending
4155	Pending	5122	In Progress
4155	Pending	5123	In Progress
5112	In Progress	5125	In Progress

The ID1 can have multiple ID2 values underneath. Each ID (ID1 or ID2) can have a single status. 

I have created a measure as you will see below that when I am applying it as a filter to a raw data table shows only the ID1 that Shows ID's 1 that are currently in Status : "Pending" but all the ID's 2 are in the status "In Progress" (so it does not have any ID 2 in the status "Pending"). At the moment that filter would show only 2 lines for the ID 1 = 4155

To create the measure I first created an extra column in the table that shows the ID 2 that are "In Progress" but the ID 1 is not "In Progress as below:

Extra = IF('Table'[Status ID2] = "In Progress" && 'Table'[Status ID1] <> "In Progress", 1, BLANK())

Produces:

ID 1	Status ID1	ID 2	Status ID2	Extra
1233	Complete	3213	Complete
2131	Pending	4351	Pending
5112	In Progress	5236	Pending
4155	Pending	5122	In Progress	1
4155	Pending	5123	In Progress	1
5112	In Progress	5125	In Progress

Then I created the following measure and apply it to my table in the report to show only values that are greater than 0

VAR valid = CALCULATE(DISTINCTCOUNT('Table'[ID 2]), 'Table'[Extra]  = 1)

Var Total = DISTINCTCOUNT('Table'[ID 2])

Var ID1 = IF(MAX('Table'[Status ID1]) = "In Progresss",1,BLANK())

Return

IF(valid=Total && ID1 <> 1 , 1, BLANK())

That measure will return the following in the table that I have as visual in my report:

ID 1	Status ID1	ID 2	Status ID2
4155	Pending	5122	In Progress
4155	Pending	5123	In Progress

Now what I need to do is to create a measure that will show me in a card that I have (for the example above) one ID 1 that is currently meet that criteria 

ID 1 Meet Criteria

         1

Could you please help on how to write that measure?

Thanks

[v-xicai]

Hi @Kostas ,

You may create measure like DAX below.

ID 1 Meet Criteria = CALCULATE(SUMX('Table', IF( MAX( 'Table'[Status ID2]) = "In Progress" && MAX('Table'[Status ID1]) <> "In Progress", 1, 0 ) ) )

Best Regards,

Amy 

Community Support Team _ Amy

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

[Kostas]

Hello,

By doing that the measure will count every row that the ID 2 has a different status from the ID1.

My issue is that I need to count all the ID's 1 that all the ID's 2 underneath them have a different status.

For example 

if the ID 1 status is "Pending" and the have two ID's 2 underneath it one as "Pending" and one as "In Progress" then it does not meet the criteria. If both ID's 2 are "In Progress" then the ID 1 meets the criteria and must be counted within the card. Please be aware that the ID 1 can appear multiple times within my table as it can contain multiple ID 2. 

Thanks

Kostas