topic Counting specific outcome from a measure that is in another table in DAX Commands and Tips

Counting specific outcome from a measure that is in another table

Anonymous — Mon, 26 Jul 2021 13:32:39 GMT

Hi,

I have a matrix table that pulls together and aggregates various columns from different tables and in the same matrix table a measure that returns a yes/no for each row within the table.

What I would like to do next is create a new measure in a card above that does a count of the measure in the matrix table for all the "Yes" outcomes.

Have not been able to crack it or find anything online to help, so hoping someone on here can help? 🙂

Thanks,

Ben

Re: Counting specific outcome from a measure that is in another table

mahoneypat — Mon, 26 Jul 2021 23:47:37 GMT

Please try a measure expression like this for your card, where column1 and column2, etc. are the columns used in your matrix visual.

Yes Count =
COUNTROWS (
    FILTER (
        SUMMARIZE ( Table, Table[Column1], Table[Column2] ),
        [Your YesNo Measure] = "Yes"
    )
)

Pat

Re: Counting specific outcome from a measure that is in another table

Anonymous — Wed, 28 Jul 2021 08:08:44 GMT

Thanks for the response mahoneypat but I couldn’t get that to work, I just get a BLANK return in the card. I think I might not be filling in the DAX correctly so I've provided more detail on my matrix table below.

Rows:

Table Core: Name

Table Core: Type

Table Core: Size

Values

Table 2000: Units

Table 2001: Units

Table 2000: Cost

Table 2001: Cost

Measure 1: Variance

Measure 2: Variance Status ‘Yes/No’

So how would I structure that DAX to count measure 2 for the number of Yes?

Re: Counting specific outcome from a measure that is in another table

mahoneypat — Wed, 28 Jul 2021 11:09:28 GMT

Thanks for the additional info. Still not totally clear on your scenerio but please try this expression

Yes Count =
COUNTROWS (
    FILTER (
        SUMMARIZE ( Core, Core[Name], Core[Type], Core[Size] ),
        [Variance Status ‘Yes/No’] = "Yes"
    )
)

Pat

Re: Counting specific outcome from a measure that is in another table

Anonymous — Wed, 28 Jul 2021 12:27:29 GMT

Thanks again , appreciate your patience 😊 however still can't get this working. I've tried to add more information below to help understand what I've got.

I’m working with 3 tables;

The Main which I'm using 3 fields; Name, Type and Size and then the other two tables link to this in a 1-2-1 relationship based on a unique ref number. These other two tables are for previous year and current year and hold quantities and costs.

Finally I have two measures, the first being a variance of the cost for year and year in a percentage format and then the second measure is checking which are out of tolerance by yes/no and it’s a count of this second measure that I want to get and add into a card.

Then added into my matrix table, aggregated as shown below.

Matrix Row	Matrix Row	Matrix Row	Matrix Value	Matrix Value	Matrix Value	Matrix Value	Matrix Value	Matrix Value
Name	Type	Size	2020 Units	2021 Units	2020 AVG Cost	2021 AVG Cost	Variance	Tolerance Out
Fitzgernal inc	commerical	large	100	150	98.33	105	7%	Yes

I have tried the expression you have suggested but it just returns (BLANK)

Re: Counting specific outcome from a measure that is in another table

Anonymous — Wed, 28 Jul 2021 13:09:04 GMT

Yay sucess! I'm not sure exactly what I did differently but it's working now. Thanks for the help mahoneypat

Ben