Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I woulkd be so greatful, if one of the DAX experts can help with a DAX formula for 2 columns I want to create.
I have 2 tables.
table 1 is called 'Staff' with 4 staff (A to D) in colum Staff, there are columns for Items Sold, Days Worked, Days To Meet Target and Date Target Met.
The related table is called 'Rota' has columns Staff, Items Sold and Date Worked.
I need a DAX to formula for 3 additional columns in Staff table:
In the Staff Table below,
Staff 'C' shows them meeting target of 6 after working 1 day, they met thier target on 2nd Feb, they worked 2 days in total.
Staff 'D' shows them meeting target of 6 after working 2 days, the 2nd day they worked was 4th April, they worked 3 days in total.
Staff
Staff | Items Sold | Days Worked | Days To Meet Target | Date Target Met |
A | 3 | 2 | ||
B | 2 | 1 | ||
C | 9 | 2 | 1 | 02-Feb-20 |
D | 7 | 3 | 2 | 04-Apr-20 |
Rota
Staff | Items Sold | Date Worked |
A | 1 | 13-Jan-20 |
C | 6 | 02-Feb-20 |
B | 2 | 14-Feb-20 |
D | 3 | 18-Feb-20 |
A | 2 | 21-Mar-20 |
D | 3 | 04-Apr-20 |
C | 3 | 05-Jun-20 |
D | 1 | 01-Jul-20 |
Solved! Go to Solution.
@Gangsta PBIX is attached below signature. If this helps, please vote for my sticker! Banana Pickle Math - Microsoft Fabric Community
Days Worked = COUNTROWS( DISTINCT( SELECTCOLUMNS( RELATEDTABLE( Rota ), "__Date", [Date Worked] ) ) )
Days To Meet Target =
VAR __Target = 6
VAR __Table = RELATEDTABLE( 'Rota' )
VAR __Table1 =
ADDCOLUMNS(
RELATEDTABLE( 'Rota' ),
"__Cumulative",
VAR __Date = [Date Worked]
VAR __Table = FILTER( __Table, [Date Worked] <= __Date )
VAR __Result = SUMX( __Table, [Items Sold] )
RETURN
__Result
)
VAR __Result = IF( MAXX( __Table1, [__Cumulative]) < __Target, BLANK(), COUNTROWS( FILTER( __Table1, [__Cumulative] < __Target ) ) + 1)
RETURN
__Result
Date Target Met =
VAR __Target = 6
VAR __Table = RELATEDTABLE( 'Rota' )
VAR __Table1 =
ADDCOLUMNS(
RELATEDTABLE( 'Rota' ),
"__Cumulative",
VAR __Date = [Date Worked]
VAR __Table = FILTER( __Table, [Date Worked] <= __Date )
VAR __Result = SUMX( __Table, [Items Sold] )
RETURN
__Result
)
VAR __Result = MINX( FILTER( __Table1, [__Cumulative] >= __Target ), [Date Worked] )
RETURN
__Result
@Greg_Deckler Thank you so much, I done, am well pleased with your solution and voted for your sticker. A quick test of all three columns shows they work as required, although the 'Date Target Met' has the time which I won't need, and likely to get in the way of afew queries so would be good to have just the date, not sure if it is easy to add this in the formula or should I do this in helper column that reformats it to just date.
Again am very greatful for you to helping me out on this, saved me alot of head scratching since admittingly I am very new to DAX.
@Gangsta Thanks! Easiest way to remove the time is to just change the Format for the column to Short date.
@Gangsta PBIX is attached below signature. If this helps, please vote for my sticker! Banana Pickle Math - Microsoft Fabric Community
Days Worked = COUNTROWS( DISTINCT( SELECTCOLUMNS( RELATEDTABLE( Rota ), "__Date", [Date Worked] ) ) )
Days To Meet Target =
VAR __Target = 6
VAR __Table = RELATEDTABLE( 'Rota' )
VAR __Table1 =
ADDCOLUMNS(
RELATEDTABLE( 'Rota' ),
"__Cumulative",
VAR __Date = [Date Worked]
VAR __Table = FILTER( __Table, [Date Worked] <= __Date )
VAR __Result = SUMX( __Table, [Items Sold] )
RETURN
__Result
)
VAR __Result = IF( MAXX( __Table1, [__Cumulative]) < __Target, BLANK(), COUNTROWS( FILTER( __Table1, [__Cumulative] < __Target ) ) + 1)
RETURN
__Result
Date Target Met =
VAR __Target = 6
VAR __Table = RELATEDTABLE( 'Rota' )
VAR __Table1 =
ADDCOLUMNS(
RELATEDTABLE( 'Rota' ),
"__Cumulative",
VAR __Date = [Date Worked]
VAR __Table = FILTER( __Table, [Date Worked] <= __Date )
VAR __Result = SUMX( __Table, [Items Sold] )
RETURN
__Result
)
VAR __Result = MINX( FILTER( __Table1, [__Cumulative] >= __Target ), [Date Worked] )
RETURN
__Result
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
17 | |
17 | |
16 |
User | Count |
---|---|
28 | |
27 | |
18 | |
14 | |
14 |