Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.              I understand I might need to use a DAX query for this, but how can...
Need Help
Show and Tell
lc_finance's avatar
lc_finance
Solution Sage
6 years ago

Hi Anonymous ,

 

You can download my proposed solution from here.

 

Here are the steps:

1) add an Index column in Power Query

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

 

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

 

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's avatar
Anonymous
Not applicable
6 years ago

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.

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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)

       

       

       

       
      • lc_finance's avatar
        lc_finance
        Solution Sage
        6 years ago

        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