topic Re: Creating custom table with multiple columns where the columns are not logically related in DAX Commands and Tips

Creating custom table with multiple columns where the columns are not logically related

DanielPf — Tue, 24 Sep 2024 16:35:30 GMT

Hello,

I'm trying to put together a custom table with 12 columns, one column for each month.

Each column should have a distinct list of account names, where a certain condition is met. I have a base table let's call it Data that has three columns: Customer Name, Due Date, FY.

I want to display the distinct list of customer names in the first column (January), where the Month(Data[Due Date]) = 1,

I want to display the distinct list of customer names in the second column (February), where the Month(Data[Due Date]) = 2, and etc.

I am trying it with

NewTable = CALCULATETABLE(VALUES('Data'[Customer Name]), Month('Data'[Due Date])=1 && ('Data'[FY] = "FY25" ) and it gives me one column with the distinct customer name values for January, but i don't know how to add the rest of the 11 columns for each month.

I'm quite new to this, so any help would be appreciated.

Thank you! 🙂

Re: Creating custom table with multiple columns where the columns are not logically related

HotChilli — Tue, 24 Sep 2024 17:08:58 GMT

Post some sample data (not a picture) and show your desired result please.

Can I ask what a row represents in this table? It sounds like each column is unrelated to other columns row-wise and it makes me wonder if what you are trying to do is a good idea.

Re: Creating custom table with multiple columns where the columns are not logically related

SachinNandanwar — Tue, 24 Sep 2024 17:12:51 GMT

you could probably create a custom table

NewTable = ADDCOLUMNS( Data, "Jan","Condition for month of Jan", "Feb","Condition for month of Feb", . . . "Dec","Condition for month of Dec")

Re: Creating custom table with multiple columns where the columns are not logically related

DanielPf — Tue, 24 Sep 2024 17:28:56 GMT

Hello,

Input:

Customer Name	Due Date	Month(Due Date) - not in data - only for example	FY
Customer 1	01.01.2024	January	FY25
Customer 2	01.01.2024	January	FY25
Customer 3	01.01.2024	January	FY25
Customer 4	01.01.2024	January	FY25
Customer 5	01.01.2024	January	FY25
Customer 6	01.01.2024	January	FY25
Customer 7	01.07.2024	July	FY25
Customer 8	01.07.2024	July	FY25
Customer 9	01.07.2024	July	FY25
Customer 10	01.10.2024	Oct	FY25
Customer 1	01.10.2024	Oct	FY25
Customer 2	01.12.2024	Dec	FY25
Customer 3	01.12.2024	Dec	FY25
Customer 4	01.12.2024	Dec	FY25
Customer 5	01.09.2024	Sept	FY25
Customer 6	01.05.2024	May	FY25
Customer 7	01.05.2024	May	FY25
Customer 8	01.04.2024	April	FY25
Customer 9	01.04.2024	April	FY25
Customer 10	01.12.2024	December	FY25

Output:

January	February	March	April	May	June	July	August	Sept	Oct	Nov	Dec
Customer 1			Customer 8	Customer 6		Customer 7		Customer 5	Customer 10		Customer 2
Customer 2			Customer 9	Customer 7		Customer 8			Customer 1		Customer 3
Customer 3						Customer 9					Customer 4
Customer 4											Customer 10
Customer 5
Customer 6




Distinct(Customer name) where Month(Due date) = 1											Distinct(Customer name) where Month(Due date) = 12

This output view is used to plan which customer needs attention each month.

Thank you 🙂

Re: Creating custom table with multiple columns where the columns are not logically related

DanielPf — Tue, 24 Sep 2024 17:36:35 GMT

I was trying to use add columns like this:
NewTable = ADDCOLUMNS(Schedule, "January", Filter(DISTINCT(Customer Name), Month(Due Date) = 1), but this gave me an error : A table of multiple values was supplied where a single value was expected.

Re: Creating custom table with multiple columns where the columns are not logically related

SachinNandanwar — Tue, 24 Sep 2024 18:05:30 GMT

You should do something like this

"January", CALCULATE(VALUES('Data'[Customer Name]), MONTH('Data'[Due Date]) = 1 && 'Data'[FY] = "FY25")

Re: Creating custom table with multiple columns where the columns are not logically related

Anonymous — Wed, 25 Sep 2024 05:26:12 GMT

Hi DanielPf

Please try this:
Here's the sample table:

Table:

In powerquery, remove the [Due Date] and [FY] columns:

Then click the fx and paste the following code in it:

Table.Group(#"Removed Columns", {"Month(Due Date) - not in data - only for example"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}})

Expand the Count column:

Finally, select the Month column and click Pivot Column in the Transform pane:

Remove the Index Column and Close&Apply.

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Creating custom table with multiple columns where the columns are not logically related

DanielPf — Wed, 25 Sep 2024 08:21:34 GMT

Thank you vey much, this actually helped!