Forum Discussion
Returning value from another table (multiple value but setting a preference what to return)
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
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/
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.
5 Replies
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] ) ) )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 ?
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.
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 ?
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/
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.
