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

Measures insert into 2D table

Hi All,

 

I have a bunch of measures, which are fairly complex in creation.

i.e. Perform maths on a table, selecting certain rows / values to create travel times based on a bunch of average speeds.

 

Now I have 6 Measures (will be more soon), Road 1 Travel Time By Car, Road 1 Travel Time By Cycle, Road 1 Travel Time By Walk, Road 2 Travel Time By Car, Road 2 Travel Time By Cycle, Road 2 Travel Time By Walk.

 

I want to be able to show them in a 2D table / matrix + a label for the road name.

 

Road Name, Car Travel Time, Cycle Travel Time, Walk Travel Time

Road 1       , 5 mins             ,   8 mins                 , 19 mins

Road 2      , 4 mins               , 7 mins                 , 15 mins

 

My Issue is that I cant seem to visualise / group my measures into rows, when i am adding them to tables / matrixs they end up in one long row.

 

Any help would be appreciated.

 

I am thinking i might want to:

- import my measures into a new source table so I can group them by rows,

- Use a visualisation tool which i havent found which allows me to display the measure where I want in a table view.

 

As a back up i can use a bunch of cards and place them where I want, but this looses the ability to scroll.

 

Thanks for your help,

Aiden

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Measures insert into 2D table

Probably use a variation of the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

 

So, have your Roads in a table, use that for your rows. Create a measure using your existing measures and SWITCH to determine which measure to show.

 

Very difficult to be precise without sample data, etc. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Measures insert into 2D table

Probably use a variation of the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

 

So, have your Roads in a table, use that for your rows. Create a measure using your existing measures and SWITCH to determine which measure to show.

 

Very difficult to be precise without sample data, etc. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Frequent Visitor

Re: Measures insert into 2D table

Perfect thanks,

 

Knew it would be fairly easy once i found the right key word!

 

My code for the car column

 

Car Travel Time =
IF(
// This first IF condition forces the measure to evaluate in the context of the visual
    HASONEVALUE(roadnames[Roads]),
// This next condition avoids getting the error that the visual cannot be displayed
        IF(HASONEVALUE(roadnames[Roads]),
// This switch statement uses the values from our Attendance slicer to determine which measure to display
            SWITCH(
          VALUES(roadnames[Roads]),
          "Botley Road",[Botley Car],
          "Iffley Road",[Iffley Car]
                ),
// If the Attendance slicer has not been selected, just display the date of the training
            "-"
        )
, "-")
Super easy, NOTE that the example given doesnt have the false result for the first if so i had to add that simply enough for me was to have a dash.

 

BR,

Aiden

Highlighted
Super User IV
Super User IV

Re: Measures insert into 2D table

Awesome!

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors