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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

@v-gizhi-msft,

You helped me a lot. Thank you! 😌

Anonymous
Not applicable

Thank you very much 🙂

Everything works great!

amitchandak
Super User
Super User

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
)

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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