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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

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

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 @v-yiruan-msft , 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

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.
v-yiruan-msft
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 @v-yiruan-msft 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

 

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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