Forum Discussion

MarkPalmberg's avatar
MarkPalmberg
Kudo Commander
6 years ago

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 th...
Need Help
MarkPalmberg's avatar
MarkPalmberg
Kudo Commander
6 years ago

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.

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi MarkPalmberg ,

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

Thanks,

Nathaniel

  • MarkPalmberg's avatar
    MarkPalmberg
    Kudo Commander
    6 years ago

    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", #####, #####, #####, #####}

    }
)

     

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      6 years ago

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

      Nathaniel

      • MarkPalmberg's avatar
        MarkPalmberg
        Kudo Commander
        6 years ago

        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 !