Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Using a table visual, splitting the results into 2 columns

Hello,

 

I have a table with a single column, but I want the results to be split among two columns instead.

What I have, with just a single column.What I have, with just a single column.            The layout I want from the table.The layout I want from the table.

I understand I might need to use a DAX query for this, but how can it be done?

 

Thank you!

9 REPLIES 9
lc_finance
Solution Sage
Solution Sage

Hi @Anonymous ,

 

 

what is the logic for splitting into 2 columns?

You simply want the bottom half of rows to be in the 2nd column? or something different

 

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

Anonymous
Not applicable

That is correct. Either taking the bottom half results and printing them to a second column, or just alternating printing between to columns.

Hi @Anonymous ,

 

You can download my proposed solution from here.

 

Here are the steps:

1) add an Index column in Power Query

Split columns.png

2) Go back to Power BI and add a calculated column. Here is DAX the formula for it:

Column number = 
VAR numberOfRows = COUNTROWS('Text')
RETURN IF ([Index]<=numberOfRows/2 , 1,2)

This calculated column returns 1 for the first half of the rows (to be included in column 1) and 2 for the second half of the rows (to be included in column 2).

Split columns result.png

 

3) Add 2 tables to your report. Filter the first table for column number =1 and the second table for column number=2.

Split columns filtering.png

 

That's it!

Does this help you?

 

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

Anonymous
Not applicable

@lc_financeI followed the steps now, and the list of report names changed, but the reports are still split unevenly. The split needs to happen after the slicer labeled D&M, TS, WS, and WL are applied.

 

The report names are not split evenly.The report names are not split evenly.

Hi @Anonymous ,

 

 

in that case, you can create a calculated measure instead of a calculated column.

Calculated measure take into account any filter that you apply to your Power BI.

 

You can use a slightly different DAX formula for the calculated measure:

Column number = 
VAR numberOfRows = COUNTROWS('Text')
VAR currentIndex = SELECTEDVALUE('Text'[Index])
RETURN IF (currentIndex<=numberOfRows/2 , 1,2)

 

Does this help you?

 

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

 

Anonymous
Not applicable

mearsure.PNG

@lc_finance I added the quick measure, but the report names I have are all valued at 2 now. When changing the filter to 'is 1' nothing appears, but 'is 2' produces all the reports.

Below is how I adapted your example to my data, if that helps.

 

 

 

Column number2 = 
VAR numberOfRows = COUNTROWS('master')
VAR currentIndex = SELECTEDVALUE('master'[Index.1])
RETURN IF (currentIndex<=numberOfRows/2 , 1,2)

 

 

 

 

Hi @Anonymous ,

 

 

could you share an example of your Power BI file?

That would help me to better understand the issue. You can upload it to a service like One Drive, Google Drive, Dropbox and then share the link.

 

Regards,

 

LC

Anonymous
Not applicable

@lc_finance I am not able to due to the sensativity of the data. However, I think what I need is a calculated measure.

 

To recap, I have two slicers that filtered down a list of report names into a matrix. A second matrix is being added, and the filtered report names need to be split so that one half is in matrix one, and one half in matrix two.  I don't think the correct solution is to split the underlying data but instead to have the result of the two slicers split. Would you agree?

Hi @Anonymous ,

 

Yes, I agree, we should split the result of the slicers into 2.

Calculated measures are the right way to do it because they take into account the result of all the slicers. 

 

I understand about the confidential data. In that case, could you share a sample Power BI file where you put sample/fake data?

A Power BI example would be very helpful in understanding why the measures I proposed to you do not seem to work

 

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.