Forum Discussion

stfox's avatar
stfox
Icon for Helper I rankHelper I
9 years ago
Solved

CALCUALTE - Filter arument with calculated measures

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

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    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

     

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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.