Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have two unrelated tables named TableA and TableB. I am trying to get the earliest date from TableB into a calculated column in TableA and I keep getting the incorrect date. I value should be 3/15/2020 but using the formula below I receive 01/15/2020.
GetDate = CALCULATE(MIN(TableB[CvPremExpDate]), TREATAS(
SUMMARIZE(
TableA,TableA[PL_POL_NBR],
TableA[PL_LOCATION_NBR],
TableA[PL_RISK_NBR],
TableA[GetMaxEndr]
),
TableB[PL_POL_NBR],
TableB[PL_LOCATION_NBR],
TableB[PL_RISK_NBR],
TableB[CV_ENDR_NBR]))
TableA
PL_POL_NBR | PL_LOCATION_NBR | PL_RISK_NBR | RK_VEHICLE_NBR | GetMaxENDR |
00000151854 | 9999 | 9665 | 33 | 10 |
00000151854 | 9999 | 9666 | 33 | 10 |
00000151854 | 9999 | 9667 | 33 | 10 |
00000151854 | 9999 | 9668 | 33 | 10 |
00000151854 | 9999 | 9669 | 33 | 10 |
00000151854 | 9999 | 9670 | 33 | 10 |
00000151854 | 9999 | 9671 | 33 | 10 |
00000151854 | 9999 | 9672 | 33 | 10 |
00000151854 | 9999 | 9673 | 33 | 10 |
00000151854 | 9999 | 9674 | 33 | 10 |
TableB
PL_POL_NBR | PL_LOCATION_NBR | PL_RISK_NBR | CV_ENDR_NBR | CvPremExpDate |
151854 | 9999 | 9665 | 9 | 10/8/2020 |
151854 | 9999 | 9665 | 10 | 3/15/2020 |
151854 | 9999 | 9666 | 9 | 10/8/2020 |
151854 | 9999 | 9666 | 10 | 3/15/2020 |
151854 | 9999 | 9667 | 9 | 10/8/2020 |
151854 | 9999 | 9667 | 10 | 3/15/2020 |
151854 | 9999 | 9668 | 9 | 10/8/2020 |
151854 | 9999 | 9668 | 10 | 3/15/2020 |
151854 | 9999 | 9669 | 9 | 10/8/2020 |
151854 | 9999 | 9669 | 10 | 3/15/2020 |
151854 | 9999 | 9670 | 9 | 10/8/2020 |
151854 | 9999 | 9670 | 10 | 3/15/2020 |
151854 | 9999 | 9671 | 9 | 10/8/2020 |
151854 | 9999 | 9671 | 10 | 3/15/2020 |
151854 | 9999 | 9672 | 9 | 10/8/2020 |
151854 | 9999 | 9672 | 10 | 3/15/2020 |
151854 | 9999 | 9673 | 9 | 10/8/2020 |
151854 | 9999 | 9673 | 10 | 3/15/2020 |
151854 | 9999 | 9674 | 9 | 10/8/2020 |
151854 | 9999 | 9674 | 10 | 3/15/2020 |
Solved! Go to Solution.
@Anonymous , Create a new column in tableA
minx(filter(Tableb, Tableb[PL_POL_NBR]=Tablea[PL_POL_NBR] && Tableb[PL_LOCATION_NBR]=Tablea[PL_LOCATION_NBR] && Tableb[PL_RISK_NBR] =Tablea[PL_RISK_NBR]), Tableb[CvPremExpDate])
@Anonymous , Create a new column in tableA
minx(filter(Tableb, Tableb[PL_POL_NBR]=Tablea[PL_POL_NBR] && Tableb[PL_LOCATION_NBR]=Tablea[PL_LOCATION_NBR] && Tableb[PL_RISK_NBR] =Tablea[PL_RISK_NBR]), Tableb[CvPremExpDate])
@Anonymous
please try this
Column = MINX(FILTER(TableB,TableA[PL_POL_NBR]=TableB[PL_POL_NBR]),'TableB'[CvPremExpDate])
Proud to be a Super User!
Tried your solution and I get the same incorrect date as my formula. I get 01/15/2020 instead of 03/15/2020
This should work
GetDate = CALCULATE(MIN(TableB[CvPremExpDate]), TREATAS(
SELECTCOLUMNS(TableA,
"PL_POL_NBR",TableA[PL_POL_NBR],
"PL_LOCATION_NBR",TableA[PL_LOCATION_NBR]
,"PL_RISK_NBR",TableA[PL_RISK_NBR]
,"CV_ENDR_NBR",TableA[GetMaxENDR]),
TableB[PL_POL_NBR],
TableB[PL_LOCATION_NBR],
TableB[PL_RISK_NBR],
TableB[CV_ENDR_NBR]))
Tried your suggestion and I get the same incorrect date as my original formula. The date returned is 01/15/2020 when it should be 03/15/2020.
User | Count |
---|---|
93 | |
85 | |
77 | |
68 | |
62 |
User | Count |
---|---|
112 | |
97 | |
95 | |
64 | |
58 |