Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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:
Best Regards,
Giotto
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:
Here is my test pbix file:
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:
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:
Here is my changed pbix file:
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:
Best Regards,
Giotto
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":
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!
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:
Hope this helps.
Best Regards,
Giotto
Thank you very much 🙂
Everything works great!
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
)
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)