Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lyndaGough
Frequent Visitor

Generate New table or calculated columns based on column and rows

I am very new to PowerBi and I am learning how to do the things I want by finding answers to similar issues raised in the forums and on blogs/videos.

 

I need to work out a period (start date and end date – in calculated columns) based on data from different rows and columns. I can do what I am after in nested if statements in Excel but I am struggling to work out how to do the same in PowerBI.

 

Scenario:
The data is for children whereby regular meetings occur. I need to identify a period (Safer Me CIN) based on an outcome of those meetings and the dates of those meetings.

 

Data structure:
Table - The meeting details come from answers filled in forms brought into PowerBi in ‘tbl_007SaferMeCIN – Form Details’. This table is linked (many - one) to the Person table ‘tbl_004_PersonView’

Relationship - tbl_004_PersonView[Eclipse ID] = tbl_007SaferMeCIN – Form Details[PERid]

Column Names = [PERid], [CALC_001 Date of Meeting], [CIN Meeting Number], [CALC_002 Safer Me CIN], [CIN Meeting Number]
Desired 2 Columns: [Start Date], [End Date]

Note – 3 of these columns are calculated columns – 2 as required to get the data from the source database and 1 to rank the meetings.

 

Logic:
Start Date = the date of the first meeting (CALC_001 Date of meeting) where the outcome (CALC-002 Safer Me CIN) = “Yes”. The child remains “Safer Me CIN” when the next meeting outcome remains “Yes”.
End Date = the date of the meeting minus 1 where the outcome is “No”.
Where the outcome of the meeting is “No” the child is not classed as in a period of Safer Me CIN (blank()).

 

Desired Outcome:
Originally I was wanting just 2 added calculated columns on the same table as the meeting data but thinking more logically, I would really like this data to generate a new table that can be linked back to the individual child (PersonView table).

 

New Table Desired Effect

PER IDStart DateEnd Date
100119/08/202017/09/2020
100207/12/202021/04/2021
100401/10/202021/10/2020
100416/11/202005/01/2021
100526/05/2021Blank()
100629/07/2020Blank()

 

 

Desired Effect if new calculated columns in original table

PER IDCALC_001 Date of MeetingCIN Meeting NumberCALC_002 Safer Me CINStart DateEnd Date
100119/08/20201Yes19/08/202017/09/2020
100118/09/20202NoBlank()Blank()
100128/10/20203NoBlank()Blank()
100207/12/20201Yes07/12/202021/04/2021
100213/03/20212Yes07/12/202021/04/2021
100222/04/20213NoBlank()Blank()
100302/09/20201NoBlank()Blank()
100401/10/20201Yes01/10/202021/10/2020
100422/10/20202NoBlank()Blank()
100416/11/20203Yes16/11/202005/01/2021
100406/01/20214NoBlank()Blank()
100527/04/20211NoBlank()Blank()
100526/05/20212Yes26/05/2021Blank()
100516/06/20213Yes26/05/2021Blank()
100629/07/20201Yes29/07/2020Blank()
100626/08/20202Yes29/07/2020Blank()
100622/09/20203Yes29/07/2020Blank()
100609/03/20214Yes29/07/2020Blank()

Formula used in Excel:
Start Date - =IF(AND(A3<>A2,D3="Yes"),B3,IF(AND(A3=A2,D2="No", D3="Yes"), B3,IF(AND(A3=A2, D2="Yes", D3="Yes"),H2,IF(D3="No","","Check"))))
End Date - =IF(AND(A3=A4, D3="Yes",D4="No"),(B4)-1,IF(AND(A3<> A4, D3="Yes"),"",IF(AND(A3=A4, D3="Yes", D4="Yes"),I4,IF(D3="No","","Check"))))

Note – “check” only there to spot error in formula.

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@lyndaGough,

 

This solution uses both calculated columns and a calculated table.

 

1. Create calculated column [Start Date]:

 

Start Date = 
VAR vPerID = 'tbl_007SaferMeCIN – Form Details'[PER ID]
VAR vMeetingDate = 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting]
VAR vFirstMeetingDateOutcomeYes =
    CALCULATE (
        MIN ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "Yes"
    )
VAR vPrevMeetingDate =
    CALCULATE (
        MAX ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] < vMeetingDate
    )
VAR vPrevMeetingDateOutcome =
    CALCULATE (
        MAX ( 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] = vPrevMeetingDate
    )
VAR vCountOutcomeNo =
    COUNTROWS (
        FILTER (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID] = vPerID
                && 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No"
                && 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] > vFirstMeetingDateOutcomeYes
                && 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] < vMeetingDate
        )
    )
VAR vResult =
    SWITCH (
        TRUE (),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No", BLANK (),
        ISBLANK ( vCountOutcomeNo ), vFirstMeetingDateOutcomeYes,
        vPrevMeetingDateOutcome = "No", vMeetingDate
    )
RETURN
    vResult

 

2. Create calculated column [End Date]:

 

End Date = 
VAR vMeetingDate = 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting]
VAR vNextMeetingDateOutcomeNo =
    CALCULATE (
        MIN ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No",
        'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] > vMeetingDate
    )
VAR vResult =
    SWITCH (
        TRUE (),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No"
            || ISBLANK ( vNextMeetingDateOutcomeNo ), BLANK (),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "Yes", vNextMeetingDateOutcomeNo - 1
    )
RETURN
   vResult

 

3. Create calculated table:

 

New Table = 
CALCULATETABLE (
    SUMMARIZE (
        'tbl_007SaferMeCIN – Form Details',
        'tbl_007SaferMeCIN – Form Details'[PER ID],
        'tbl_007SaferMeCIN – Form Details'[Start Date],
        'tbl_007SaferMeCIN – Form Details'[End Date]
    ),
    NOT ( ISBLANK ( 'tbl_007SaferMeCIN – Form Details'[Start Date] ) )
)

 

DataInsights_0-1627867866210.png





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

Proud to be a Super User!




View solution in original post

2 REPLIES 2
DataInsights
Super User
Super User

@lyndaGough,

 

This solution uses both calculated columns and a calculated table.

 

1. Create calculated column [Start Date]:

 

Start Date = 
VAR vPerID = 'tbl_007SaferMeCIN – Form Details'[PER ID]
VAR vMeetingDate = 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting]
VAR vFirstMeetingDateOutcomeYes =
    CALCULATE (
        MIN ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "Yes"
    )
VAR vPrevMeetingDate =
    CALCULATE (
        MAX ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] < vMeetingDate
    )
VAR vPrevMeetingDateOutcome =
    CALCULATE (
        MAX ( 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] = vPrevMeetingDate
    )
VAR vCountOutcomeNo =
    COUNTROWS (
        FILTER (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID] = vPerID
                && 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No"
                && 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] > vFirstMeetingDateOutcomeYes
                && 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] < vMeetingDate
        )
    )
VAR vResult =
    SWITCH (
        TRUE (),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No", BLANK (),
        ISBLANK ( vCountOutcomeNo ), vFirstMeetingDateOutcomeYes,
        vPrevMeetingDateOutcome = "No", vMeetingDate
    )
RETURN
    vResult

 

2. Create calculated column [End Date]:

 

End Date = 
VAR vMeetingDate = 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting]
VAR vNextMeetingDateOutcomeNo =
    CALCULATE (
        MIN ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No",
        'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] > vMeetingDate
    )
VAR vResult =
    SWITCH (
        TRUE (),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No"
            || ISBLANK ( vNextMeetingDateOutcomeNo ), BLANK (),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "Yes", vNextMeetingDateOutcomeNo - 1
    )
RETURN
   vResult

 

3. Create calculated table:

 

New Table = 
CALCULATETABLE (
    SUMMARIZE (
        'tbl_007SaferMeCIN – Form Details',
        'tbl_007SaferMeCIN – Form Details'[PER ID],
        'tbl_007SaferMeCIN – Form Details'[Start Date],
        'tbl_007SaferMeCIN – Form Details'[End Date]
    ),
    NOT ( ISBLANK ( 'tbl_007SaferMeCIN – Form Details'[Start Date] ) )
)

 

DataInsights_0-1627867866210.png





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

Proud to be a Super User!




Thank you so much for this.  It seems to be working beautifully.  Now I will study the code to learn how it works so I can write this style of code in the future.😁

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors