Forum Discussion
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...
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", #####, #####, #####, #####}
}
)
Wow! Does it work? Usually I might just have the first column on multiple rows.
Nathaniel
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 !