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.currentst...
  • 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 

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?

  • pandapanda's avatar
    pandapanda
    Regular Visitor
    8 years ago

    Yes that is correct, but what I want in the report is to show how many users have changed to status (dynamic/slicer to choose) over a certain time perid.

     

    in the data i give, 

    If I were to select the last 60 days and want to know how many users moved to status 2 then the answer I want is 2!

     

     

    • parry2k's avatar
      parry2k
      Super User
      8 years ago

      based on your sample date , answer will be 1 the scenario you explained below, no?

       

      • pandapanda's avatar
        pandapanda
        Regular Visitor
        8 years ago

        The two users  1212  and 1213  both change to status 4  (from status 1 and 2 ) in the time period (60days) 

         

        Is that clear?