Create a measure that gets the value of another measure for a specific row

[wildert]

I have a table thats used for reporting, and it needs to be filterable by date, salesman, etc in PowerBI / Excel

ReportColumn	ReportValue
Sales In US	$###
Sales in California	$###
Sales Order Count	###

so I bang up a table with three rows and a calculated measure (quasi-code)

ReportValue = switch(ReportValue,"Sales In US",<some calc to get sales only in US>,"Sales In California",<some calc to get sales only in California>,"Sales Order Count",<some calc to get sales order count>)

my problem is that I need <some calc..> to not create its own filter context, because then all my slicers in PowerBI/Excel stop working. I really wish I could use some kind of tuple, or cell address, to specify these values, but it seems like my only choice is summarize/calculate/etc. 

To put it another way, I keep hitting the problem mentioned here...

http://community.powerbi.com/t5/Desktop/how-to-create-table-Summarizecolumns-keep-filter-context/m-p...

any ideas?

[wildert]

I was having problems because I was not using a totally disconnected table, my table was actually connected to the sales table and it was not giving good behavior for the measure. A totally disconnected table behaves correctly.

[danextian · Accepted solution]

Hi  @wildert,

Try this:

Create a disconnected calculated table in DAX (no relationship with data table)-

Table = 
DATATABLE (
    "Report", STRING,
    "Index", INTEGER,
    {
        { "Sales in US", 1 },
        { "Sales in California", 2 },
        { "Sales Order Count", 3 }
    }
)

Then a switching measure

Value =
VAR SelectedMeasure =
    MIN ( 'Table'[Index] )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Report] ),
        SWITCH (
            SelectedMeasure,
            1, FORMAT ( [Sales in US], "$#,#" ),
            2, FORMAT ( [Sales in California], "$#,#" ),
            FORMAT ( [Order Count], "0,0" )
        )
    )