cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pandapanda Frequent Visitor
Frequent Visitor

Re: SQL to DAX - iterative table query

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

Highlighted
Moderator v-caliao-msft
Moderator

Re: SQL to DAX - iterative table query

@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