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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
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 

11 REPLIES 11
Super User
Super User

Re: SQL to DAX - iterative table query

@pandapanda 

 

 

Can you share sample data for both the tables?





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




pandapanda Frequent Visitor
Frequent Visitor

Re: SQL to DAX - iterative table query

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

 

 

Super User
Super User

Re: SQL to DAX - iterative table query

i guess in this sample your output will be

 

Status   #of changes

1                 1

2                 1

4                 2

 

correct?





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Highlighted
pandapanda Frequent Visitor
Frequent Visitor

Re: SQL to DAX - iterative table query

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!

 

 

Super User
Super User

Re: SQL to DAX - iterative table query

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

 





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




pandapanda Frequent Visitor
Frequent Visitor

Re: SQL to DAX - iterative table query

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 Frequent Visitor
Frequent Visitor

Re: SQL to DAX - iterative table query

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!

 

 

Super User
Super User

Re: SQL to DAX - iterative table query

@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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




pandapanda Frequent Visitor
Frequent Visitor

Re: SQL to DAX - iterative table query

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)

 

help1.jpg