Forum Discussion

PragatiS050's avatar
PragatiS050
Regular Visitor
9 years ago

SQL query to DAX

Hello friends,

 

I am new to Power BI. I am stuck in a problem. 

 

Problem Statement:

 

Student

Class

AdmittedDate

ReleaseDate

A

1

2 jan 2016

3 jan 2016

A

2

3 Jan 2016

5 jan 2016

A

3

4 jan 2016

5 jan 2016

B

1

5 jan 2016

7 jan 2016

B

2

6 Jan 2016

10 jan 2016

B

3

7 jan 2016

8 jan 2016

c

1

3 Jan 2016

4 jan 2016

C

2

null

5 jan 2016

C

3

4 jan 2016

null

 

Now I want to select Start-class and End-Class in slicer as:

Start

1

End

3

 

Now I have to calculate the number of total days each student spent from Class1-3. This user can change on the basis of slicer like 2-3. Based on Class selected by the user, I need to find Cycle time as DateDiff between AdmittedDate of START in slicer and ReleaseDate for END in slicer from above table.

 

 

For A: DateDiff will be DATEDIFF(2 jan 2016, 5 Jan 2016,Day)+1

For C: DateDiff will be null as Release date is null.

 

 

 

Start

2

End

3

 

For A: DateDiff will be DATEDIFF(3 jan 2016, 5 Jan 2016,Day)+1

 

Once this datediff is calculated dynamically based on value of slicer, We have to calculate average of the Cycle time calculated which will be sliced by other columns present in the table.

 

NOTE: If Admitted Date is null or Release Date is null Student will not be counted in average

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Expected result measure: =
VAR _condition =
    COUNTROWS (
        FILTER ( Data, Data[AdmittedDate] = BLANK () || Data[ReleaseDate] = BLANK () )
    ) > 0
RETURN
    IF (
        _condition,
        BLANK (),
        COUNTROWS ( CALENDAR ( MIN ( Data[AdmittedDate] ), MAX ( Data[ReleaseDate] ) ) )
    )