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

[d1x0nl30ng]

i have a scenario where i need to return a state of a something from a state tracker (i.e. table 1 below to table 2)

as it will return multiple value for certain items, how could the Dax return value based on below scenario ?

(i. if the item has Active state at all, always return Active & if there is no Active state but other multiple state , return either 1 -maybe first hit of state alphabetical order)

been googling around, the nearest is to use FIRSTNONBLANK, but i cant assure Active state is always the first hit as in the case of SVR1 below

[Jihwan_Kim · Accepted solution]

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]
        )
    )
)

[v-yangliu-msft · Accepted solution]

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-me...

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.

[d1x0nl30ng]

i created another calculated column from Table2 = measure, and was able to use that calculated column as the visual filter. 

Looks to be working, but is there any cons by using this way ?

[Greg_Deckler]

@d1x0nl30ng Maybe:

State Measure = 
  VAR __SVR = MAX('Table 2'[Svr])
  VAR __States = DISTINCT(SELECTVALUES(FILTER('Table 1',[Svr] = __SVR),"State",[State]))
RETURN
  SWITCH(TRUE(),
    COUNTROWS(__States) = 1,MAXX(__States,[State]),
    "Active" IN __States,"Active",
    "Hibernate" IN __States,"Hibernate",
    "Retired"
  )

You would need an index or date or something to get "first". You could do that with a DAX index or something like it.

[Jihwan_Kim · Accepted solution]

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]
        )
    )
)

[d1x0nl30ng]

Thanks, this measure work. I create the measure on the main data (i.e. table 1) added a relationship to table 2 and was able to showing correctly on a table visual)

I have a noobie question though, if i were doing a card visual to show the number of SVR which does not have an Active state 

count of SVR as the value on card , i added the measure to the visual filter, it doesnt look to be working. Is it fundamentally wrong to use a measure as a visual filter ?