Get column values from table

Anything related to PowerPivot and DAX Formuale
Seven440
Posts: 2
Joined: Wed Aug 07, 2019 8:32 pm

Get column values from table

Postby Seven440 » Wed Aug 07, 2019 8:42 pm

Hi all!

The task seems simple, but search in forums and DAX guide didn't help me.
There is the only table Sales with several columns (Item, Store, Area, Month, Flag1, Flag2, Value).
I try to add calculated column with next expression:

=SUMX(
FILTER(Sales;
EARLIER(Sales[Month]) = Sales[Month] &&
[Flag1] = "No" &&

Sales[Store] in distinct(SELECTCOLUMNS(
FILTER(Sales;
EARLIER(Sales[Month]) = Sales[Month]&&
EARLIER(Sales[Area]) = Sales[Area] &&
EARLIER(Sales[Item]) = Sales[Item] &&
Sales[Flag2] = 1);
"Store";Sales[Store])

)
);
Sales[Value])

The problem is with condition
Sales[Store] in distinct(SELECTCOLUMNS(
...

I can't find a function instead of SELECTCOLUMNS to get a list of values in Store column.
Further, I planned to get unique values in the column (with DISTINCT) to make a list of values and use it to check Sales[Store] (with IN).

The file with example is attached.

Please help.
Attachments
Sales_example.xlsx
(285.96 KiB) Downloaded 4 times

Adam
Posts: 2
Joined: Sat Aug 10, 2019 6:44 pm

Re: Get column values from table

Postby Adam » Sat Aug 10, 2019 7:39 pm

Hi, can you describe what the output should be in each cell?

Seven440
Posts: 2
Joined: Wed Aug 07, 2019 8:32 pm

Re: Get column values from table

Postby Seven440 » Mon Aug 12, 2019 5:21 pm

Adam {L_WROTE}{L_COLON}Hi, can you describe what the output should be in each cell?


Hi, Adam!
In each cell should be the sum of all values in column Sales[Value] if all of following conditions are TRUE:
1) Value in column Sales[Month] is equal to current row value of column Sales[Month].
2) Value in column [Flag1] = "No".
3) Value in column Sales[Store] are in the list of outputs of column Sales[Store] with another filter of the whole Sales table where all of following conditions are TRUE:
a) Value in column Sales[Month] is equal to current row value of column Sales[Month].
b) Value in column Sales[Area] is equal to current row value of column Sales[Area].
c) Value in column Sales[Item] is equal to current row value of column Sales[Item].
d) Value in column Sales[Flag2] = 1.

Hope, it helps to understand.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests