Forum Discussion

mlm0926's avatar
mlm0926
Frequent Visitor
2 years ago
Solved

Convert Table from Visualisation into Actual table?

Hi all, 

 

I'm not too sure how to explain this properly. 

I have a standard bar chart that looks at aggregated values. 

 

I want to get the values based on "Show as table" information and create a table from it: 

 

Is there any way we I can fetch the values using dax? 

Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi mlm0926 

    The table shown when selecting "Show as table" contains the same values you would see in a Table visual containing the same fields as the column chart. Behind the scenes, the DAX query for "Show as table" is essentially identical to the DAX query for a Table visual (without totals).

     

    The only difference in appearance with "Show as table" is that the axis field values are shown as a comma-separated list for each aggregated value.

     

    So you should be able to see essentially the same values if you make a copy of the column chart and convert it to a Table, without having to write any additional DAX.

     

    Does this work for you?

     

    Regards

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    Hi mlm0926 

    The table shown when selecting "Show as table" contains the same values you would see in a Table visual containing the same fields as the column chart. Behind the scenes, the DAX query for "Show as table" is essentially identical to the DAX query for a Table visual (without totals).

     

    The only difference in appearance with "Show as table" is that the axis field values are shown as a comma-separated list for each aggregated value.

     

    So you should be able to see essentially the same values if you make a copy of the column chart and convert it to a Table, without having to write any additional DAX.

     

    Does this work for you?

     

    Regards

    • mlm0926's avatar
      mlm0926
      Frequent Visitor
      2 years ago

      Hi, I think I didn't explain properly. Your solution is correct as well but I'm wondering how I can create a table in the data based on the table from "Show as table" instead of the table visualisation? 

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    Hi again mlm0926 

    Out of interest, why do you want to add a table to the model based on the "Show as table" table?
    Generally this is not necessary, but there are special cases where you might want to do this (e.g. Aggregations with a DirectQuery source).

     

    To answer your question though, you could capture the DAX query from the visual and create a DAX calculated table using the relevant part of the code, omitting some of the unnecessary parts of the query.

    For example, this kind of DAX expression would be the simplest way you could generate the table:

     

    Summarized Table = // change to appropriate name
SUMMARIZECOLUMNS (
    'Date'[Year],
    'Date'[Month],
    "A12", [A12] // rename if desired
)