cancel
Showing results for
Did you mean:
Helper III

## Calculating data between multiple tables and columns

Hello,

There are two tables:

Table "Purchases":

 Method Type NetworkDays (calculated) A AA 8 B BB 2 C CC 19 A AB 6 A AA 1

Table "Terms":

 Method Type Term A AA 5 A AB 10 B BB 10 C CC 15

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
Community Support

Hi,

``````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:

Best Regards,

Giotto

11 REPLIES 11
Community Support

Hi,

``````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:

Here is my test pbix file:

Hope this helps.

Best Regards,

Giotto

Helper III

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

Community Support

Hi,

``````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:

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

``````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:

Here is my changed pbix file:

Hope this helps.

Best Regards,

Giotto

Helper III

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

Community Support

Hi,

``````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:

Best Regards,

Giotto

Helper III

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

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

 Method Type NetworkDays (calculated) According to another schedule A AA 8 B BB 2 0 C CC 19 1 A AB 6 A AA 1 0

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!

Community Support

Hi,

``````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:

Hope this helps.

Best Regards,

Giotto

Helper III

You helped me a lot. Thank you! 😌

Helper III

Thank you very much 🙂

Everything works great!

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
)

Proud to be a Super User!

Helper III

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)

Announcements

#### Microsoft Business Applications Summit sessions

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