ROW and/or UNION function blocking SELECTEDVALUE

[Anonymous]

I have created a table using the Union and Rows functions, first row is:

KEY FIGURES =

UNION(

ROW("A","","B", "£m","C","% (commitment)","D","£m","E", "% (commitment)","F", "%", "G", 1),

The table contains various measures and am trying to filter each measure by using a bridge table and the SELETEDVALUE function, because i need to recreate the visual for many different types and don't want to duplicate every measure.

An example of a measure is: 

Current Value=

VAR Current_Date = Max(Table[Reference_Date])

VAR RESULT = CALCULATE(SUM(Table[value])/1000000,

FILTER(Table,Table[Reference_Date]<=Current_Date),

FILTER(Table,Table[Type] = SELECTEDVALUE('Type Bridge'[Type])))

RETURN

RESULT

The measure is working and shows the correct value using a card visual, but if i add it to the Union/row table mentioned above, the cell is blank. 

Any help appreciated.

[lbendlin]

What is the UNION for? And shouldn't this table rather be done in Power Query?  More like 

[Anonymous]

Thanks for your reply. The reason for the UNION is to group a variety of measures and values from different data sources into the same table, in order for me to use the table visual. The visual i need to create does not lend itself to transforming the data in power query, as far as i understand. 

The reasoning behind using UNION aside, are you aware of any instance where the use of the UNION or ROW can block the SELECTEDVALUE function, which is what seems to be happening?

Thank you in advance

[lbendlin]

Sounds like you are coming from a Qlik background. Their UX paradigms do not translate well into Power BI.

SELECTEDVALUE is used in measures and cannot be applied to manifested tables. It only makes sense in table variables inside measures.

[Anonymous]

Ok, thank you. In that case, are you able to offer any suggestions for how i might solve the issue? The table I am creating is 5x9 cells and I have 29 of them to produce, so approx 1000 measures if i have to do them individually! All tables have the same measures, but are filtered by type. Which is why i am looking to use a bridge table to filter for each type, rather than replicating 29 sets of measures. 

Any thoughts or ideas much appreciated

[lbendlin · Accepted solution]

This may be difficult if you are just starting with Power BI but you may want to look into using Calculation Groups. They were designed for such a scenario.

[Anonymous]

Thank you - using calculation groups looks promising and I have used to achieve part of what I set out to do.