Dear Community Members, I have two Queries, called: Org_Tbl, RepTrn_Tbl. Org_tbl has the customers' names, while the RepTrn_Tbl has the attendances and reports information. It has a one to money relation. I want to create a column to check if these customers are active or not in past period, where I have athird Query record the attendance and report dates. It is something such as this IF statement: ActiveStatus = IF(Org_Tbl[JoinDiffDate]<=3 && SUM(RepTrn_Tbl[Trn_MeetingAttend])<=3 && SUM(RepTrn_Tbl[Trn_WsAct])>=1,"Active",IF(Org_Tbl[JoinDiffDate] > 3 && Org_Tbl[JoinDiffDate] < 6 && SUM(RepTrn_Tbl[Trn_WsAct])>=4 && SUM(RepTrn_Tbl[Trn_MeetingAttend])>=4,"Active",IF(Org_Tbl[JoinDiffDate] > 6 && SUM(RepTrn_Tbl[Trn_MeetingAttend]) >= 4 && SUM(RepTrn_Tbl[Trn_WsAct])>=2,"Active","Inactive"))) I know it has to group by RepTrn_Tbl[Org_ID], but I am new to DAX and I could not find the solution. Any other suggestion, appreciated!.Thanks for your support in advance!Best Regards Mahmoud

Forum Discussion

mahmoud

Helper I

5 years ago

Solved

Group by and Conditions

Dear Community Members,

I have two Queries, called: Org_Tbl, RepTrn_Tbl. Org_tbl has the customers' names, while the RepTrn_Tbl has the attendances and reports information. It has a one to money relation. I want to create a column to check if these customers are active or not in past period, where I have athird Query record the attendance and report dates.

It is something such as this IF statement:

ActiveStatus = IF(Org_Tbl[JoinDiffDate]<=3 && SUM(RepTrn_Tbl[Trn_MeetingAttend])<=3 && SUM(RepTrn_Tbl[Trn_WsAct])>=1,"Active",IF(Org_Tbl[JoinDiffDate] > 3 && Org_Tbl[JoinDiffDate] < 6 && SUM(RepTrn_Tbl[Trn_WsAct])>=4 && SUM(RepTrn_Tbl[Trn_MeetingAttend])>=4,"Active",IF(Org_Tbl[JoinDiffDate] > 6 && SUM(RepTrn_Tbl[Trn_MeetingAttend]) >= 4 && SUM(RepTrn_Tbl[Trn_WsAct])>=2,"Active","Inactive")))

I know it has to group by RepTrn_Tbl[Org_ID], but I am new to DAX and I could not find the solution. Any other suggestion, appreciated!.

Thanks for your support in advance!

Best Regards

Mahmoud

Need Help

Anonymous

5 years ago

Hi mahmoud ,

In fact, , I created a measure rather than a calculated column in my previous post. If you need to create a calculated column, you can write it like this:

Column for ActiveStatus = 
VAR _curdiffd = 'Org_Tbl'[JoinDiffDate]
VAR _trnma =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_MeetingAttend] ),
        FILTER ( 'RepTrn_Tbl', 'RepTrn_Tbl'[Org_ID] = 'Org_Tbl'[Org_ID] )
    )
VAR _trnwsa =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_WsAct] ),
        FILTER ( 'RepTrn_Tbl', 'RepTrn_Tbl'[Org_ID] = 'Org_Tbl'[Org_ID] )
    )
RETURN
    IF (
         ( _curdiffd <= 3
            && _trnma <= 3
            && _trnwsa >= 1 )
            || ( _curdiffd > 3
            && _curdiffd < 6
            && _trnwsa >= 4
            && _trnma >= 4 )
            || ( _curdiffd > 6
            && _trnma >= 4
            && _trnwsa >= 2 ),
        "Active",
        "Inactive"
    )

Best Regards

Anonymous

5 years ago

Hi mahmoud ,

As checked your screenshot, there are some slicers applied in your report page. The value of a calculate column is computed during data refresh, it will not change by the user interaction in the report dynamically. So please create a measure instead of calculated column.

Calculated Columns and Measures in DAX

New measure for ActiveStatus =
VAR _curdiffd =
    SELECTEDVALUE ( 'Org_Tbl'[JoinDiffDate] )
VAR _trnma =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_MeetingAttend] ),
        FILTER (
            'RepTrn_Tbl',
            'RepTrn_Tbl'[Org_ID] = SELECTEDVALUE ( 'Org_Tbl'[Org_ID] )
        )
    )
VAR _trnwsa =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_4WsAct] ),
        FILTER (
            'RepTrn_Tbl',
            'RepTrn_Tbl'[Org_ID] = SELECTEDVALUE ( 'Org_Tbl'[Org_ID] )
        )
    )
VAR _trnws =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_4Ws] ),
        FILTER (
            'RepTrn_Tbl',
            'RepTrn_Tbl'[Org_ID] = SELECTEDVALUE ( 'Org_Tbl'[Org_ID] )
        )
    )
RETURN
    IF (
         ( _curdiffd <= 3
            && _trnws <= 3
            && _trnma <= 3
            && _trnwsa >= 1 )
            || ( _curdiffd > 3
            && _curdiffd < 6
            && _trnwsa >= 4
            && _trnma >= 4 )
            || ( _curdiffd > 6
            && _trnws >= 4
            && _trnma >= 4
            && _trnwsa >= 2 ),
        "Active",
        "Inactive"
    )

Best Regards

5 Replies

Anonymous

Not applicable

5 years ago

Hi mahmoud ,

Which column be based on when create relationship between these two table? Customer name field? If yes, please create a measure as below:

ActiveStatus =
VAR _curdiffd =
    SUM ( 'Org_Tbl'[JoinDiffDate] )
RETURN
    IF (
         (
            _curdiffd <= 3
                && SUM ( RepTrn_Tbl[Trn_MeetingAttend] ) <= 3
                && SUM ( RepTrn_Tbl[Trn_WsAct] ) >= 1
        )
            || (
                _curdiffd > 3
                    && _curdiffd < 6
                    && SUM ( RepTrn_Tbl[Trn_WsAct] ) >= 4
                    && SUM ( RepTrn_Tbl[Trn_MeetingAttend] ) >= 4
            )
            || (
                _curdiffd > 6
                    && SUM ( RepTrn_Tbl[Trn_MeetingAttend] ) >= 4
                    && SUM ( RepTrn_Tbl[Trn_WsAct] ) >= 2
            ),
        "Active",
        "Inactive"
    )

If the above one is not working in your scenario, please provide some sample data(exclude sensitive data) in Org_Tbl and RepTrn_Tbl table and the calculation logic about ActiveStatus. Thank you.

Best Regards

mahmoud

Helper I

5 years ago

Dear Anonymous thanks for your support!

Kindly find in the following image the realtions bewtween the three tables.

The logice you wrote for the IF statement is totally correct except the SUM of JoinDiffDate, this is a number to measure how long the organization has been registered

Thanks for your support in advance!

Best Regards

Mahmoud

Anonymous

Not applicable

5 years ago

Hi mahmoud ,

In fact, , I created a measure rather than a calculated column in my previous post. If you need to create a calculated column, you can write it like this:

Column for ActiveStatus = 
VAR _curdiffd = 'Org_Tbl'[JoinDiffDate]
VAR _trnma =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_MeetingAttend] ),
        FILTER ( 'RepTrn_Tbl', 'RepTrn_Tbl'[Org_ID] = 'Org_Tbl'[Org_ID] )
    )
VAR _trnwsa =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_WsAct] ),
        FILTER ( 'RepTrn_Tbl', 'RepTrn_Tbl'[Org_ID] = 'Org_Tbl'[Org_ID] )
    )
RETURN
    IF (
         ( _curdiffd <= 3
            && _trnma <= 3
            && _trnwsa >= 1 )
            || ( _curdiffd > 3
            && _curdiffd < 6
            && _trnwsa >= 4
            && _trnma >= 4 )
            || ( _curdiffd > 6
            && _trnma >= 4
            && _trnwsa >= 2 ),
        "Active",
        "Inactive"
    )

Best Regards

mahmoud

Helper I

5 years ago

Hi Anonymous , Hi v-yingjl

I hope you are doing well,

I am sorry for getting back to you late, I was off in the past days.

Thanks for your support and respond, the new solution worked partially, as you see in the following image.

Actually, an extra condition I did not mention it, as guess the filter control will did it. The extra condition is to apply these rules on organization for past six months.

Here is updated code I used

Column for ActiveStatus New = 
VAR _curdiffd = 'Org_Tbl'[JoinDiffDate]
VAR _trnma =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_MeetingAttend] ),
        FILTER ( 'RepTrn_Tbl', 'RepTrn_Tbl'[Org_ID] = 'Org_Tbl'[Org_ID] )
    )
VAR _trnwsa =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_4WsAct] ),
        FILTER ( 'RepTrn_Tbl', 'RepTrn_Tbl'[Org_ID] = 'Org_Tbl'[Org_ID] )
    )
VAR _trnws =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_4Ws] ),
        FILTER ( 'RepTrn_Tbl', 'RepTrn_Tbl'[Org_ID] = 'Org_Tbl'[Org_ID] )
    )
RETURN
    IF (
         (     _curdiffd <= 3
            && _trnws <= 3
            && _trnma <= 3
            && _trnwsa >= 1 )
            || ( _curdiffd > 3
            && _curdiffd < 6
            && _trnwsa >= 4
            && _trnma >= 4 )
            || ( 
               _curdiffd > 6
            && _trnws >= 4
            && _trnma >= 4
            && _trnwsa >= 2 ),
        "Active",
        "Inactive"
    )

Thanks for your support in advance!

Best Regards

Mahmoud

Anonymous

Not applicable

5 years ago

Hi mahmoud ,

Calculated Columns and Measures in DAX

New measure for ActiveStatus =
VAR _curdiffd =
    SELECTEDVALUE ( 'Org_Tbl'[JoinDiffDate] )
VAR _trnma =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_MeetingAttend] ),
        FILTER (
            'RepTrn_Tbl',
            'RepTrn_Tbl'[Org_ID] = SELECTEDVALUE ( 'Org_Tbl'[Org_ID] )
        )
    )
VAR _trnwsa =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_4WsAct] ),
        FILTER (
            'RepTrn_Tbl',
            'RepTrn_Tbl'[Org_ID] = SELECTEDVALUE ( 'Org_Tbl'[Org_ID] )
        )
    )
VAR _trnws =
    CALCULATE (
        SUM ( RepTrn_Tbl[Trn_4Ws] ),
        FILTER (
            'RepTrn_Tbl',
            'RepTrn_Tbl'[Org_ID] = SELECTEDVALUE ( 'Org_Tbl'[Org_ID] )
        )
    )
RETURN
    IF (
         ( _curdiffd <= 3
            && _trnws <= 3
            && _trnma <= 3
            && _trnwsa >= 1 )
            || ( _curdiffd > 3
            && _curdiffd < 6
            && _trnwsa >= 4
            && _trnma >= 4 )
            || ( _curdiffd > 6
            && _trnws >= 4
            && _trnma >= 4
            && _trnwsa >= 2 ),
        "Active",
        "Inactive"
    )

Best Regards

Forum Discussion

Group by and Conditions

5 Replies

Related content

Conditional grouping

Grouped summaries with conditional filtering

Fabric & Azure Data User Group Program Requirements & Benefits

Conditional formatting using multiple calculation groups

Community User Group Experience