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

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)

     

  • pandapanda's avatar
    pandapanda
    Regular Visitor
    8 years ago

    For the three use cases

    1.start is 8th nov and end date is 12th November  there are 2 two customers (id 1 and 2) that change to Regular hence  the answer is 2

    2. 8th nov and end date is 10th November no one changes to regular hence 0 (in fact here id3 gooes to sleeper)

    3. Expected result if date start is 11th nov and end date is 12th November is 2 NOT 1 as per the sample sheet answer!! as id1 and 3 go to regular

     

    Sorry!!

    but i am sure the main data set will be easier to get some numbers

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    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