topic Re: Counts of ID if ID has values in more than 1 value in a different column in DAX Commands and Tips

Counts of ID if ID has values in more than 1 value in a different column

syasmin25 — Thu, 07 Jan 2021 21:39:17 GMT

Hello,

I am trying to count ID’s that have a “D or F” in more than 1 or more subjects.

I have the following table,

IDPeriodGradeBldg#Subject

101	S1	A	2356	English
102	S1	D	2356	Math
102	S1	F	2356	Science
102	S1	F	2356	English
103	S1	B	2356	Science
103	S2	A	2154	English
104	S1	B	2356	English
104	S2	B	2154	Science
105	S1	C	2356	Math
105	S2	B	2154	English
105	S2	F	2154	Science
106	S2	A	2154	Science

& I have created the following visual. I am trying to create a column/measure that counts the ID if they have have a “D or F” in English and Math or even English, Math and Science. I am also trying to create a column that holds " English and Math" & “English, Math & Science” as values. I am trying to insert a table that would hold those values underneath the table in the image below and calculate their f%. I tried creating conditional columns to separate subjects and then doing an unpivot but realized that it was creating duplicate ID’s and also each ID has subject in a different row. Any help would be appreciated.

I can't seem to attach the file here.

Re: Counts of ID if ID has values in more than 1 value in a different column

littlemojopuppy — Thu, 07 Jan 2021 22:16:35 GMT

Hi syasmin25

Try this...

Failing Grade Count = VAR StudentTermSummary = SUMMARIZE( ALLSELECTED(Grades), Grades[ID], Grades[Period], "FailingGrades", CALCULATE( COUNTROWS(Grades), Grades[Grade] IN {"D", "F"} ) ) RETURN CALCULATE( DISTINCTCOUNT(Grades[ID]), FILTER( StudentTermSummary, [FailingGrades] > 0 ), VALUES(Grades[Period]) )

Re: Counts of ID if ID has values in more than 1 value in a different column

syasmin25 — Thu, 07 Jan 2021 22:54:05 GMT

Thank you for your help. Unfortunately, I am actually wanting the table to look somewhat like the image below. This is where I am struggling with counts as unpivoting them creates duplicates.

Re: Counts of ID if ID has values in more than 1 value in a different column

littlemojopuppy — Thu, 07 Jan 2021 23:04:06 GMT

syasmin25 how about this?

Failing Grade Count = VAR StudentTermSummary = SUMMARIZE( ALLSELECTED(Grades), Grades[ID], Grades[Subject], "FailingGrades", CALCULATE( COUNTROWS(Grades), Grades[Grade] IN {"D", "F"} ) ) RETURN CALCULATE( DISTINCTCOUNT(Grades[ID]), FILTER( StudentTermSummary, [FailingGrades] > 0 ), VALUES(Grades[Subject]) )

Re: Counts of ID if ID has values in more than 1 value in a different column

syasmin25 — Thu, 07 Jan 2021 23:12:35 GMT

Thank you again, this seems to make a lot of sense. However, I would like to make combination as the image below, how would I create a table like that to do the counts using the the raw data posted above in the question?

Re: Counts of ID if ID has values in more than 1 value in a different column

amitchandak — Fri, 08 Jan 2021 02:37:11 GMT

syasmin25 , for this one you might have create two meaures with filters, with formula suggested in last post and then use those measures in matrix, and use Option "Show on Row"

Re: Counts of ID if ID has values in more than 1 value in a different column

littlemojopuppy — Fri, 08 Jan 2021 03:36:01 GMT

Hey amitchandak dd you notice where someone else offered a solution??? 😐

Re: Counts of ID if ID has values in more than 1 value in a different column

amitchandak — Fri, 08 Jan 2021 03:49:56 GMT

littlemojopuppy , Again no overlap with your Answer. I just suggested display way. Check - "with formula suggested in last post"

Re: Counts of ID if ID has values in more than 1 value in a different column

littlemojopuppy — Fri, 08 Jan 2021 04:21:30 GMT

🙄

Re: Counts of ID if ID has values in more than 1 value in a different column

syasmin25 — Thu, 14 Jan 2021 06:37:34 GMT

Hello,

I apologize for the late response, the "IN" statement here seems to count if they have failed either D or F, I am trying to count when there is both D and F.