Forum Discussion

RK9009's avatar
RK9009
Frequent Visitor
5 years ago

Dax Help

Greeting PBI Community, I need help with this dax measure studentID status abc123 status 1  abc123 status 2 abc123 status 3 abc123 status 4 bca231 status 2 bca231 status ...
RK9009's avatar
RK9009
Frequent Visitor
5 years ago

It's ok if we use a measure per status:
starting with: distinct status1=calc(countdistinct(studentID),Status="status 1")

Expected Result :
Status1 = 2
Status2 = 3
Status3 = 2
Status4 = 2

Fowmy's avatar
Fowmy
Super User
5 years ago

RK9009 

Please recheck your results: Following Measure works for me:

Total Count = 
VAR DT1 = 
CALCULATETABLE(
    DISTINCT('Table1 (2)'[studentID]),
    FILTER(ALL('Table1 (2)'[status]), 'Table1 (2)'[status] < SELECTEDVALUE('Table1 (2)'[status]))
)

VAR DT2 = 
CALCULATETABLE(
    DISTINCT('Table1 (2)'[studentID]),
    FILTER(ALL('Table1 (2)'[studentID]),
        NOT 'Table1 (2)'[studentID] IN DT1 ) 
)
VAR D2 =  COUNTROWS(DT2)

RETURN

D2

 

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply šŸ™‚

YouTube  LinkedIn

 

  • RK9009's avatar
    RK9009
    Frequent Visitor
    5 years ago

    Fowmy thank you so much for the reply, can you please specify what the status numbers are in the measure;

     

    Total Count = 
VAR DT1 = 
CALCULATETABLE(
    DISTINCT('Table1 (2)'[studentID]),
    FILTER(ALL('Table1 (2)'[status]), 'Table1 (2)'[status] < SELECTEDVALUE('Table1 (2)'[status]))
)

VAR DT2 = 
CALCULATETABLE(
    DISTINCT('Table1 (2)'[studentID]),
    FILTER(ALL('Table1 (2)'[studentID]),
        NOT 'Table1 (2)'[studentID] IN DT1 ) 
)
VAR D2 =  COUNTROWS(DT2)

RETURN

D2
    • Fowmy's avatar
      Fowmy
      Super User
      5 years ago

      RK9009 

       

      No need to specify, when you drop this measure against the status in a table visual as I did, it calculates based on the status value. The status1 is lower than status2 and so on. You can add more status values  in the same order if needed. 

      let me know if you need any help after the testing  it. 

      • RK9009's avatar
        RK9009
        Frequent Visitor
        5 years ago

        Does it work if it is a has to follow a hierarchy as in 

        All id in A being first to all ID in D.