cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mahmoud
Helper I
Helper I

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

2 ACCEPTED SOLUTIONS
yingyinr
Community Support
Community Support

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"
    )

yingyinr_0-1615279681147.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
mahmoud
Helper I
Helper I

Hi @yingyinr , 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"
    )

 

 

 Screenshot 2021-03-16 130126.png

Thanks for your support in advance!

Best Regards

Mahmoud

yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dear @yingyinr thanks for your support!

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

mahmoud_0-1615196813712.png

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

 

yingyinr
Community Support
Community Support

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"
    )

yingyinr_0-1615279681147.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

Ted's Dev Camp - October 6, 2022

Watch the playback of Session 26 with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors