Forum Discussion

scoder's avatar
scoder
Frequent Visitor
2 years ago
Solved

How to select a applicable value from different table based on given condition

I have two dataset importted in power bi Dataset1 keeps the appliable loaded amount for evreyday use and this has two dates start and end date which shows from when to when this amount is aplicabl...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

    Order amount measure: = 
SUM( 'Order'[order amount] )

     

     

    Load amount measure: =
VAR _currentdate =
    MAX ( 'Calendar'[Date] )
VAR _loadtable =
    FILTER (
        Load,
        Load[Allotted date] <= _currentdate
            && Load[Start Date] <= _currentdate
            && Load[End Date] >= _currentdate
    )
VAR _lastallotteddate =
    MAXX ( _loadtable, Load[Allotted date] )
VAR _laststartdate =
    MAXX ( _loadtable, Load[Start Date] )
RETURN
    SUMX (
        FILTER (
            _loadtable,
            Load[Allotted date] = _lastallotteddate
                && Load[Start Date] = _laststartdate
        ),
        Load[Loaded_Amount]
    )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
2 years ago

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

 

 

 

Order amount measure: = 
SUM( 'Order'[order amount] )

 

 

Load amount measure: =
VAR _currentdate =
    MAX ( 'Calendar'[Date] )
VAR _loadtable =
    FILTER (
        Load,
        Load[Allotted date] <= _currentdate
            && Load[Start Date] <= _currentdate
            && Load[End Date] >= _currentdate
    )
VAR _lastallotteddate =
    MAXX ( _loadtable, Load[Allotted date] )
VAR _laststartdate =
    MAXX ( _loadtable, Load[Start Date] )
RETURN
    SUMX (
        FILTER (
            _loadtable,
            Load[Allotted date] = _lastallotteddate
                && Load[Start Date] = _laststartdate
        ),
        Load[Loaded_Amount]
    )