Forum Discussion

DominykasPoc's avatar
DominykasPoc
New Member
2 years ago
Solved

Return value based on Name and criteria

Dear community, I am asking for help to get the right formula for the solution below (example made in Excel, but formula solution is needed in PBI): Table1 (left) - Contains a person and his ac...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi DominykasPoc ,
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:

    Create two columns

    Min DateDiff = 
VAR Datediff_from = ABS(DATEDIFF('Table'[From],TODAY(),DAY))
VAR Datediff_to = ABS(DATEDIFF('Table'[To],TODAY(),DAY))
RETURN
IF(
    Datediff_from < Datediff_to,
    Datediff_from,
    Datediff_to
)
    Status Today = 
VAR minDatediff = 
CALCULATE(
    MIN('Table'[Min DateDiff]),
    ALLEXCEPT(
        'Table',
        'Table'[NameSurname]
    )
)
RETURN
CALCULATE(
    MAX('Table'[Status]),
    FILTER(
        'Table',
        'Table'[Min DateDiff] = minDatediff
    )
)

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     



Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi DominykasPoc ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create two columns

Min DateDiff = 
VAR Datediff_from = ABS(DATEDIFF('Table'[From],TODAY(),DAY))
VAR Datediff_to = ABS(DATEDIFF('Table'[To],TODAY(),DAY))
RETURN
IF(
    Datediff_from < Datediff_to,
    Datediff_from,
    Datediff_to
)
Status Today = 
VAR minDatediff = 
CALCULATE(
    MIN('Table'[Min DateDiff]),
    ALLEXCEPT(
        'Table',
        'Table'[NameSurname]
    )
)
RETURN
CALCULATE(
    MAX('Table'[Status]),
    FILTER(
        'Table',
        'Table'[Min DateDiff] = minDatediff
    )
)

Final output

 

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 



DominykasPoc's avatar
DominykasPoc
New Member
2 years ago

Hi Albert,

Very smart way of identifying the minimum difference, great idea. 
I was struggling at first to find whats not working on my real data file. 

I did changed abit the calculation of Status_Today of the RETURN phase. Inserted the fix here so in case someone will be facing the same. 

Albert, maybe you could comment why did this small change made an effect of the result? Because with your example I was getting wrong results (formula worked, but the results were wrong, in some cases I was getting "status" which wasnt present in the data for that particular Name/Surname at all).

 

 


Much appreciated,
Dominykas