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

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?

     

  • pandapanda's avatar
    pandapanda
    Regular Visitor
    8 years ago

    Your previous post: 

    Status   #of changes

    1                 1

    2                 1

    4                 2

     

    correct?

    ----------------

     

    Those are the right numbers - i want to get that last result -  #changes is 2 where status is 4 for that time period!

     

     

    • parry2k's avatar
      parry2k
      Super User
      8 years ago

      pandapanda in table visual add status, and then add status again and change the aggregation of 2nd status to distinct count

       

      add date slicer and your table visual will filter based on the date range you select in date slicer

      • pandapanda's avatar
        pandapanda
        Regular Visitor
        8 years ago

        I have tried  - I add time slicer based on a time table and use it but it gives me really low number ( am expecting 00's not 1 or 2)