topic Re: how to count a measure based on the measure itself in DAX Commands and Tips

how to count a measure based on the measure itself

yishenhui — Sat, 20 May 2023 04:02:30 GMT

Hi,

Below screenshot is from a measure table, "TotalScore", "ScoreRange" are measures. I want to count one of the measures based on the measure. For example, if I count totalscore, I want to return ((99.6%, 2), (99.0%,2), (97.9%,1)...) How can I do that?

Re: how to count a measure based on the measure itself

tamerj1 — Sat, 20 May 2023 05:41:57 GMT

Hi yishenhui

let's assume that this is a student data (this is irrelevant anyway but only to explain)

first create the following calculated table

Marks =
SELECTCOLUMNS ( GENERATESERIES ( 0.001, 0.001, 1 ), "Mark", [Value] )

then use the following measure

Count =
COUNTROWS (
FILTER (
ADDCOLUMNS (
CROSSJOIN ( VALUES ( 'Table'[Student ID] ), VALUES ( Marks[Mark] ) ),
"@Score", [TotalScore]
),
[Mark] = [@Score]
)
)

Re: how to count a measure based on the measure itself

yishenhui — Sat, 20 May 2023 17:18:23 GMT

Hi Tamerj,

I understand your DAX but not sure why it's still not working. (I adjusted the Marks table a little

Marks = SELECTCOLUMNS ( GENERATESERIES ( 0.000, 1.001,0.001 ), "Mark", [Value] ) as the max value should be the second place and 0 and 100 is an accept-able value.)

however, the result gives empty. See below screenshot, the column scorescount supposes give the count number when it's the same as the score. I have created table step by step and found once apply the filter, the table goes to empty. I even added another column with [Mark]-[totalscore], it does show the difference between the two value. However once I apply the filter for that column value 0, the table goes to empty. (I used a table visual to monitor it). The DAX is as

Table 2 = ADDCOLUMNS ( CROSSJOIN ( VALUES ( DataQuery[analystname] ), VALUES ( Marks[Mark] ) ), "@Score", [TotalScore],"mark-score",[Mark]-[TotalScore] )

Any idea where is the issue?

)

Re: how to count a measure based on the measure itself

yishenhui — Sat, 20 May 2023 19:26:47 GMT

Hi Tamerj,

I got more progress. The totalscore is a calculated value which usually is not exact match the mark.value. I used another column scorerange which is result of floor. I got some count numbers and found they are not match the manual count result. I found I need to add column "year/month" to the table as well. Finally I got the correct numbers. I will mark your answer as solution as the method you provided is the key to reslove it. Thanks!

Re: how to count a measure based on the measure itself

tamerj1 — Sat, 20 May 2023 19:39:21 GMT

yishenhui

Plaxe the column Mark in a new table visual along with the Count measure