cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkPalmberg Regular Visitor
Regular Visitor

DATATABLE code help

Hi.

I'm just getting started with DATATABLE and am wondering about the following scenario:

Column A has 4 rows

1

2

3

4

Column B has 10 rows, multiple values for columns 3 (5) and 4 (3).

Is this doable with DATATABLE? Do I need Table Constructor instead? Here's what I have so far; I need multiple Gift Levels for my "Big" row...:

 

Gift Table = 
DATATABLE (
    "Gift Level Name", STRING,
    "Gift Level", STRING,
    "Gifts Needed", INTEGER,
    "Prospects Needed", INTEGER,
    "Dollars Needed", CURRENCY,
    "Cumulative Total", CURRENCY,
    {
        { "Biggest", "#####", #####, #####, #####, #####},
        { "Bigger", "#####", #####, #####, #####, ##### },
        { "Big", "#####", #####, #####, #####, #####}
    }
)

Thank you!

7 REPLIES 7
Super User IV
Super User IV

Re: DATATABLE code help

@MarkPalmberg ,

Not sure I understand what you are asking.

 

Here is what I get. Seems pretty cool.

 

Term	Definition
ColumnName	Any DAX expression that returns a table.
DataType	An enumeration that includes: INTEGER, DOUBLE, STRING, BOOLEAN, CURRENCY, DATETIME
Value	A single argument using Excel syntax for a one dimensional array constant, nested to provide an array of arrays. This argument represents the set of data values that will be in the table

For example,
{ {values in row1}, {values in row2}, {values in row3}, etc. }
Where {values in row1} is a comma delimited set of constant expressions, namely a combination of constants, combined with a handful of basic functions including DATE, TIME, and BLANK, as well as a plus operator between DATE and TIME and a unary minus operator so that negative values can be expressed.

The following are all valid values: 3, -5, BLANK(), "2009-04-15 02:45:21". Values may not refer to anything outside the immediate expression, and cannot refer to columns, tables, relationships, or anything else.

A missing value will be treated identically to BLANK(). For example, the following are the same: {1,2,BLANK(),4} {1,2,,4}

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

datatable.PNG





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

Proud to be a Super User!




Super User IV
Super User IV

Re: DATATABLE code help

Unlike DATATABLE, Table Constructor allows any scalar expressions as input values. Seems like you have to enumerate the type in DATATABLE.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





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

Proud to be a Super User!




MarkPalmberg Regular Visitor
Regular Visitor

Re: DATATABLE code help

Sorry I wasn't very clear, @Nathaniel_C ! I need a table with multiple "Gift Level" values for my "Bigger" value in the "Level Name" column.

 

Thanks for taking a look at this; I appreciate your time.

Super User IV
Super User IV

Re: DATATABLE code help

Hi @MarkPalmberg ,

I guess I still don't understand. Could you dummy up a picture of your expected outlook?

Thanks,

Nathaniel





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

Proud to be a Super User!




MarkPalmberg Regular Visitor
Regular Visitor

Re: DATATABLE code help

Reading your last reply turned on a lightbulb for me, @Nathaniel_C . *My* issue was that I was trying to shoehorn an Excel format into a Power BI table; see below. The answer simply seems to be (duh) to create multiple instances of my Bigger row:

Gift Table = 
DATATABLE (
    "Gift Level Name", STRING,
    "Gift Level", STRING,
    "Gifts Needed", INTEGER,
    "Prospects Needed", INTEGER,
    "Dollars Needed", CURRENCY,
    "Cumulative Total", CURRENCY,
    {
        { "Biggest", "#####1", #####, #####, #####, #####},
        { "Bigger", "#####1", #####, #####, #####, ##### },
        { "Bigger", "#####2", #####, #####, #####, ##### },
        { "Bigger", "#####3", #####, #####, #####, ##### },
        { "Big", "#####1", #####, #####, #####, #####},
        { "Big", "#####2", #####, #####, #####, #####},
        { "Big", "#####3", #####, #####, #####, #####}

    }
)

 

bbb.PNG

Super User IV
Super User IV

Re: DATATABLE code help

Wow! Does it work? Usually I might just have the first column on multiple rows.

Nathaniel





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

Proud to be a Super User!




MarkPalmberg Regular Visitor
Regular Visitor

Re: DATATABLE code help

Yes, "first column in multiple rows" is how it's going to have to work in Power BI, I think, Which is probably fine, maybe in a Matrix visualization? Now the trick will be if I can populate my numeric values with VARs when I create the table. I was thinking I read somewhere that I can. Like:

 

Gift Table = 
VAR BIGGER1 =
CALCULATE(
    SUM(SOMECOLUMN),
    SOMEFILTER)
RETURN
DATATABLE (
    "Gift Level Name", STRING,
    "Gift Level", STRING,
    "Gifts Needed", INTEGER,
    "Prospects Needed", INTEGER,
    "Dollars Needed", CURRENCY,
    "Cumulative Total", CURRENCY,
    {
        { "Biggest", "#####1", #####, #####, #####, #####},
        { "Bigger", BIGGER1, #####, #####, #####, ##### },
        { "Bigger", "#####2", #####, #####, #####, ##### },
        { "Bigger", "#####3", #####, #####, #####, ##### },
        { "Big", "#####1", #####, #####, #####, #####},
        { "Big", "#####2", #####, #####, #####, #####},
        { "Big", "#####3", #####, #####, #####, #####}

    }
)

Thanks for your replies, @Nathaniel_C !

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors