cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wildert Frequent Visitor
Frequent Visitor

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

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

 

 ReportColumnReportValue
 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?

1 ACCEPTED SOLUTION

Accepted Solutions
danextian New Contributor
New Contributor

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

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

 

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

2 REPLIES 2
danextian New Contributor
New Contributor

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

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

 

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

wildert Frequent Visitor
Frequent Visitor

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

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)