CALCUALTE - Filter arument with calculated measures

[stfox]

Hi Folks

I want to create a measure that quantifies the progress of students through training milestones. Essentially I want to quantify the number of distinct students that have reached

1.  Milestone 4 in a) 2 or more subject areas b) 3 or more subject areas
2. Milestone 5 in a) 2 or more subject areas b) 3 or more subject areas

My first tthought is that this should be DAX CALCULATE measure. So to calculate the number of Distinct students that have reached milestone 4 in 2 (1a above) - the formula should be something like

1a = CALCULATE(DISTINCTCOUNT(Sheet1[Student_ID]), DISTINCTCOUNT(Sheet1[Subject area]>1, DISTINCTCOUNT(Sheet1[Max Milestone Reached]= 4)))

But I get the following error message "The DISTINCTCOUNT function only accepts a column reference as an argument". So it looks like it doesn't like a measure as the filter argument.

Can someone help me out with this measure?

The structure of the data is shown in the screen shot below,and the PowerBI workbook is available at this link

Cheers

Steve

[OwenAuger · Accepted solution]

Hi @stfox

Two ways of writing measure 1a off the top of my head:

1a = 
COUNTROWS (
    FILTER (
        VALUES ( Sheet1[Student_ID] ),
        CALCULATE (
            DISTINCTCOUNT ( Sheet1[Subject area] ),
            Sheet1[Max Milestone Reached] = 4
        )
            >= 2
    )
)

1a v2 = 
CALCULATE (
    COUNTROWS (
        FILTER (
            VALUES ( Sheet1[Student_ID] ),
            CALCULATE ( DISTINCTCOUNT ( Sheet1[Subject area] ) ) >= 2
        )
    ),
    Sheet1[Max Milestone Reached] = 4
)

Since I had to filter the Student_IDs, I just used COUNTROWS(FILTER(VALUES(...))), rather than DISTINCTCOUNT.

Cheers,

Owen

[Greg_Deckler]

Hmm, this is a tricky context problem and my brain is apparently not working at full throttle tonight. The context issue is that you want your first filter in the context of per student but the overall measure in the context of all. Here's as far as my brain would allow me to go with this tonight. Will sleep on it and see if my brain works better tomorrow.

1a = CALCULATE(DISTINCTCOUNT(Students[Student_ID]), Students[Max Milestone Reached]= 4)

This will give you the distinct count of Student_ID for those students that have reached milestone 4 in something but it could be only in 1 thing.