Forum Discussion

Terrassa's avatar
Terrassa
Icon for Helper I rankHelper I
2 years ago
Solved

Get data from another table with several filters

Hello,   I have two tables related to Bill Of Materials, one with the structure and another one with the revisions of the child components and their validity dates: Table BOM: Parent  Child ...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi Terrassa - create one new date table with some range using calendar function. 

     

    check the model and relationship with the new date table.

    use the below measure to get the active revision and call the date table call it in selectedvalue(datetable[date]). 

    ActiveRevision =
    VAR SelectedDate = SELECTEDVALUE(DateTable[Date])
    RETURN
    CALCULATE(
    MAX(Revisions[Revision]),
    FILTER(
    Revisions,
    Revisions[Material] = MAX(BOM[Child]) &&
    Revisions[Start] <= SelectedDate &&
    (Revisions[End] >= SelectedDate || ISBLANK(Revisions[End]))
    )
    )

     

    try the above measure and let know if it works 

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
2 years ago

Hi Terrassa - create one new date table with some range using calendar function. 

 

check the model and relationship with the new date table.

use the below measure to get the active revision and call the date table call it in selectedvalue(datetable[date]). 

ActiveRevision =
VAR SelectedDate = SELECTEDVALUE(DateTable[Date])
RETURN
CALCULATE(
MAX(Revisions[Revision]),
FILTER(
Revisions,
Revisions[Material] = MAX(BOM[Child]) &&
Revisions[Start] <= SelectedDate &&
(Revisions[End] >= SelectedDate || ISBLANK(Revisions[End]))
)
)

 

try the above measure and let know if it works 

  • Terrassa's avatar
    Terrassa
    Icon for Helper I rankHelper I
    2 years ago

    Thank you very much Rajendraongole1, it works perfectly!