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 ...
Fowmy's avatar
Fowmy
Super User
5 years ago

RK9009 

Do you need a single measure that calculates one result or is against each Status?
Can you share the expected output as well?

________________________

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

    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

    • RK9009's avatar
      RK9009
      Frequent Visitor
      5 years ago

      Sorry Status 2 =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