Returning value from another table (multiple value but setting a preference what to return)

Hi,

Please check the below picture and the attached pbix file.

State measure: =
IF (
    HASONEVALUE ( Data[Svr] ),
    SWITCH (
        TRUE (),
        COUNTROWS ( VALUES ( Data[State] ) ) = 1, SELECTEDVALUE ( Data[State] ),
        { "Active" } IN VALUES ( Data[State] ), "Active",
        MAXX (
            GROUPBY (
                Data,
                Data[State],
                "@earliestindex", MINX ( CURRENTGROUP (), Data[Index] )
            ),
            Data[State]
        )
    )
)

Hi  d1x0nl30ng ,

About the difference between calculated column and measure, you can check the following link:

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

https://truecue.com/resources/blog/what-is-the-difference-between-power-bi-calculated-columns-and-measures/

Here are the steps you can follow:

1. You can use measure instead:

Flag =
var _table1=FILTER(ALL('Table'),'Table'[Svr]='Table'[Svr]&&'Table'[State]="Active")
return
IF(
MAX('Table'[Svr]) in SELECTCOLUMNS(_table1,"svr",[Svr]),0,1)

Count =
CALCULATE(DISTINCTCOUNT('Table'[Svr]),ALLSELECTED('Table'))

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Please click here for the pbix file.

Best Regards,

Liu Yang

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