Forum Discussion

pandapanda's avatar
pandapanda
Regular Visitor
8 years ago
Solved

SQL to DAX - iterative table query

I have SQL like this

 

select count(*) from myTABLE f1 join myTABLE f2
  on f1.customerid = f2.customerid
where f1.reportingdate='2017-10-31' and f2.reportingdate='2017-10-30'
  and f1.currentstatus='1' and f2.currentstatus <>'1'

 

I want to get this as a measure in PowerBI desktop.

The dates should be dynamic from a date heirachy slider (table already created)

 

How can i get this is DAX

 

 

  • v-caliao-msft's avatar
    v-caliao-msft
    8 years ago

    pandapanda,

     

    It's seems someone provide a solution for your requirement on your another thread, pelase check it.
    http://community.powerbi.com/t5/Desktop/Report-number-of-changes-in-a-field/m-p/310698#M137616

     

    number of users that change to currentstatus regular =
VAR summizedTbl =
    SUMMARIZE (
        yourTable,
        yourTable[customerid],
        "Distinct Status count", DISTINCTCOUNT ( yourTable[currentstatus] ),
        "status", CONCATENATEX (
            yourTable,
            yourTable[currentstatus],
            "/",
            yourTable[reportingdate], DESC
        )
    )
VAR rowCnt =
    COUNTROWS (
        FILTER (
            summizedTbl,
            [Distinct Status count] > 1
                && LEFT ( [status], 7 ) = "Regular"
        )
    )
RETURN
    IF ( ISBLANK ( rowCnt ), 0, rowCnt )

     

    Regards,

    Charlie Liao 

11 Replies

    • pandapanda's avatar
      pandapanda
      Regular Visitor
      8 years ago

      Its only 1 table!! i am using it against itself to work out differences

       

      The table is like this

       

      ID       customerID   Date                Status

      1             1212          2/3/2016         1

      2             1213          2/3/2016         2

      3             1212          2/4/2016         4

      4             1213          2/4/2016         4

       

      For every day, you get row for each customer

      over time the status changes

       

      What I want to know is the changes to and from status. 

      so i want on PowerBI a number where the number of changes to status X happens over a period of time

       

       

      • parry2k's avatar
        parry2k
        Super User
        8 years ago

        i guess in this sample your output will be

         

        Status   #of changes

        1                 1

        2                 1

        4                 2

         

        correct?