cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Martyna_
Helper III
Helper III

Calculating data between multiple tables and columns

Hello,

There are two tables:

 

Table "Purchases":

MethodTypeNetworkDays (calculated)
AAA8
BBB2
CCC19
AAB6
AAA1

 

Table "Terms":

MethodTypeTerm
AAA5
AAB10
BBB10
CCC15

 

I need to calculate:

If 'Purchases'[Method] = A, 'Purchases'[Type] = AA, then if 'Purchases'[NetworkDays] < 'Terms'[Term] where 'Terms'[Method] = A & 'Terms'[Type] = AA, tehn show "1";

If 'Purchases'[Method] = A, 'Purchases'[Type] = AB, then if 'Purchases'[NetworkDays] < 'Terms'[Term] where 'Terms'[Method] = A & 'Terms'[Type] = AB, then show "0";
etc. I should describe each situation as far as there are methods and types in table "Terms".

 

If NetworkDays more than Term - show 1, if NetworkDays less than Term - show 0.

 

What is the correct syntax for describing such a formula?

1 ACCEPTED SOLUTION

Hi,

 

Please try this measure:

Measure = 
SUMX (
    GROUPBY ( Purchases, Purchases[Method], Purchases[Type] ),
    CALCULATE (
        SWITCH (
            TRUE,
            MAX ( Purchases[NetworkDays (calculated)] ) < MAX ( Purchases[Term] ), 0,
            MAX ( Purchases[NetworkDays (calculated)] ) > MAX ( Purchases[Term] ), 1
        )
    )
)

Or try this measure2:

Measure 2 = 
SUMX (
    GROUPBY ( Purchases, Purchases[Method], Purchases[Type] ),
    CALCULATE (
        SWITCH (
            TRUE,
            MAX ( Purchases[NetworkDays (calculated)] )
                < IF (
                    MAX ( Purchases[Type] ) <> BLANK (),
                    CALCULATE (
                        MAX ( 'Terms'[Term] ),
                        FILTER (
                            'Terms',
                            'Terms'[Type] = MAX ( 'Purchases'[Type] )
                                && 'Terms'[Method] = MAX ( 'Purchases'[Method] )
                        )
                    ),
                    CALCULATE (
                        MAX ( 'Terms'[Term] ),
                        FILTER ( 'Terms', 'Terms'[Method] = MAX ( 'Purchases'[Method] ) )
                    )
                ), 0,
            MAX ( Purchases[NetworkDays (calculated)] )
                > IF (
                    MAX ( Purchases[Type] ) <> BLANK (),
                    CALCULATE (
                        MAX ( 'Terms'[Term] ),
                        FILTER (
                            'Terms',
                            'Terms'[Type] = MAX ( 'Purchases'[Type] )
                                && 'Terms'[Method] = MAX ( 'Purchases'[Method] )
                        )
                    ),
                    CALCULATE (
                        MAX ( 'Terms'[Term] ),
                        FILTER ( 'Terms', 'Terms'[Method] = MAX ( 'Purchases'[Method] ) )
                    )
                ), 1
        )
    )
)

The result shows:

5.PNG

 

Best Regards,

Giotto

View solution in original post

11 REPLIES 11
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try this measure:

Measure = 
IF (
    MAX ( Purchases[Method] ) = MAX ( Terms[Method] )
        && MAX ( Purchases[Type] ) = MAX ( Purchases[Type] ),
    IF ( MAX ( Purchases[NetworkDays (calculated)] ) < MAX ( Terms[Term] ), 0, 1 )
)

The result shows:

60.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

Thank you. Just one exception I must to insert, which I wrote upper. 🙃

Hi,

 

Please try this calculated column:

Term = 
IF (
    Purchases[Type] <> BLANK (),
    MAXX (
        FILTER (
            'Terms',
            'Terms'[Type] = 'Purchases'[Type]
                && 'Terms'[Method] = 'Purchases'[Method]
        ),
        'Terms'[Term]
    ),
    MAXX (
        FILTER ( 'Terms', 'Terms'[Method] = 'Purchases'[Method] ),
        'Terms'[Term]
    )
)

Then try this measure:

Measure = 
SWITCH (
    TRUE,
    MAX ( Purchases[NetworkDays (calculated)] ) < MAX ( Purchases[Term] ), 0,
    MAX ( Purchases[NetworkDays (calculated)] ) > MAX ( Purchases[Term] ), 1
)

The result shows:

2.PNG

And i advise you only using one measure to show the expected result for higher performance.

Please try this:

Measure 2 =
VAR a =
    IF (
        MAX ( Purchases[Type] ) <> BLANK (),
        CALCULATE (
            MAX ( 'Terms'[Term] ),
            FILTER (
                'Terms',
                'Terms'[Type] = MAX ( 'Purchases'[Type] )
                    && 'Terms'[Method] = MAX ( 'Purchases'[Method] )
            )
        ),
        CALCULATE (
            MAX ( 'Terms'[Term] ),
            FILTER ( 'Terms', 'Terms'[Method] = MAX ( 'Purchases'[Method] ) )
        )
    )
VAR b =
    MAX ( Purchases[NetworkDays (calculated)] )
RETURN
    SWITCH ( TRUE, b < a, 0, b > a, 1 )

The result shows:

3.PNG

Here is my changed pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

Thank you very much, I see the numbers "0" and "1" in the list. Everything works. 😀
But is it possible to count measure value? For example, only those that are "1" are relevant to me. I want to show how many of those “1” there are (e.g. based on certain criteria).

Hi,

 

Please try this measure:

Measure = 
SUMX (
    GROUPBY ( Purchases, Purchases[Method], Purchases[Type] ),
    CALCULATE (
        SWITCH (
            TRUE,
            MAX ( Purchases[NetworkDays (calculated)] ) < MAX ( Purchases[Term] ), 0,
            MAX ( Purchases[NetworkDays (calculated)] ) > MAX ( Purchases[Term] ), 1
        )
    )
)

Or try this measure2:

Measure 2 = 
SUMX (
    GROUPBY ( Purchases, Purchases[Method], Purchases[Type] ),
    CALCULATE (
        SWITCH (
            TRUE,
            MAX ( Purchases[NetworkDays (calculated)] )
                < IF (
                    MAX ( Purchases[Type] ) <> BLANK (),
                    CALCULATE (
                        MAX ( 'Terms'[Term] ),
                        FILTER (
                            'Terms',
                            'Terms'[Type] = MAX ( 'Purchases'[Type] )
                                && 'Terms'[Method] = MAX ( 'Purchases'[Method] )
                        )
                    ),
                    CALCULATE (
                        MAX ( 'Terms'[Term] ),
                        FILTER ( 'Terms', 'Terms'[Method] = MAX ( 'Purchases'[Method] ) )
                    )
                ), 0,
            MAX ( Purchases[NetworkDays (calculated)] )
                > IF (
                    MAX ( Purchases[Type] ) <> BLANK (),
                    CALCULATE (
                        MAX ( 'Terms'[Term] ),
                        FILTER (
                            'Terms',
                            'Terms'[Type] = MAX ( 'Purchases'[Type] )
                                && 'Terms'[Method] = MAX ( 'Purchases'[Method] )
                        )
                    ),
                    CALCULATE (
                        MAX ( 'Terms'[Term] ),
                        FILTER ( 'Terms', 'Terms'[Method] = MAX ( 'Purchases'[Method] ) )
                    )
                ), 1
        )
    )
)

The result shows:

5.PNG

 

Best Regards,

Giotto

View solution in original post

Hi @v-gizhi-msft,

I received another exception from the client that should be included in the formula.

 

Table "Purchases" have one more column "According to another schedule":

MethodTypeNetworkDays (calculated)According to another schedule
AAA8 
BBB20
CCC191
AAB6 
AAA10

 

How to insert into a formula when it doesn't matter what the method and type is, if the "According to another schedule" column is 1, the final result should be 0. 

 

Thank you very very much!

Hi,

 

Please try this:

Measure 2 =
SUMX (
    GROUPBY ( Purchases, Purchases[Method], Purchases[Type] ),
    CALCULATE (
        IF (
            MAX ( Purchases[According to another schedule] ) = 1,
            0,
            SWITCH (
                TRUE,
                MAX ( Purchases[NetworkDays (calculated)] )
                    < IF (
                        MAX ( Purchases[Type] ) <> BLANK (),
                        CALCULATE (
                            MAX ( 'Terms'[Term] ),
                            FILTER (
                                'Terms',
                                'Terms'[Type] = MAX ( 'Purchases'[Type] )
                                    && 'Terms'[Method] = MAX ( 'Purchases'[Method] )
                            )
                        ),
                        CALCULATE (
                            MAX ( 'Terms'[Term] ),
                            FILTER ( 'Terms', 'Terms'[Method] = MAX ( 'Purchases'[Method] ) )
                        )
                    ), 0,
                MAX ( Purchases[NetworkDays (calculated)] )
                    > IF (
                        MAX ( Purchases[Type] ) <> BLANK (),
                        CALCULATE (
                            MAX ( 'Terms'[Term] ),
                            FILTER (
                                'Terms',
                                'Terms'[Type] = MAX ( 'Purchases'[Type] )
                                    && 'Terms'[Method] = MAX ( 'Purchases'[Method] )
                            )
                        ),
                        CALCULATE (
                            MAX ( 'Terms'[Term] ),
                            FILTER ( 'Terms', 'Terms'[Method] = MAX ( 'Purchases'[Method] ) )
                        )
                    ), 1
            )
        )
    )
)

The result shows:

30.PNG

Hope this helps.

 

Best Regards,

Giotto

@v-gizhi-msft,

You helped me a lot. Thank you! 😌

Thank you very much 🙂

Everything works great!

amitchandak
Super User IV
Super User IV

You can get a new column in a purchase like this


Term = maxx(filter('Terms','Terms'[Type] ='Purchases'[Type] && 'Terms'[Method] ='Purchases'[Method]),'Terms'[Term] )

and do calculation based on that

like

Flag = Switch( true(),
'Purchases'[Method] = "A" && 'Purchases'[Type] = "AA" && 'Purchases'[NetworkDays] < 'Purchases'[Term] ,1,
'Purchases'[Method] = "A" && 'Purchases'[Type] = "AB" && 'Purchases'[NetworkDays] < 'Purchases'[Term] ,0,
0
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thank you for your time.

Sorry, my client changed his mind... 😒 Client....heh

Method will always be mandatory, but Type will not always be, just for some Method, for example, Method A will always have mandatory Type AA, but some Methods, for example, B will never have Type. 

So this formula is not correct in this situation:

Term = maxx(filter('Terms','Terms'[Type] ='Purchases'[Type] && 'Terms'[Method] ='Purchases'[Method]),'Terms'[Term] )

How can I insert exceptions?

 

When I will write this formula "Term", I will have this column in table "Purchases". That's very good.

After this, I will have calculate value "Late": If NetworkDays more than Term - show 1, if NetworkDays less than Term - show 0.

Will this formula be suitable for calculating the Late purchases?

Late = Switch( true();'Purchases'[NetworkDays] < 'Purchases'[Term];0;'Puchases'[NetworkDays] > 'Purchases'[Term];1;0)

 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors