topic Re: Get column values from table in DAX Commands and Tips

Get column values from table

Seven440 — Wed, 07 Aug 2019 09:54:12 GMT

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).

Please help.

Re: Get column values from table

SteveCampbell — Thu, 08 Aug 2019 15:28:09 GMT

I'm not sure quite the desired output, but it looks like you're trying to say:

Sum Sales[Value]

Where month is the current row month

and Flag1 = "No"

and the current store, month, area and item has to exist in the table with Flag2 = 1 ?

If so, I rewrote a little to optimize:

=
VAR _month = Sales[Month]
VAR _area = Sales[Area]
VAR _item = Sales[Item]
VAR _store = Sales[Store]
RETURN
    CALCULATE (
        SUM ( Sales[Value] ),
        FILTER (
            Sales,
            Sales[Month] = _Month
                && [Flag1] = "No"
                && Sales[Store]
                    IN CALCULATETABLE (
                        VALUES ( Sales[Store] ),
                        Sales[Month] = _month,
                        Sales[Area] = _area,
                        Sales[Item] = _item,
                        Sales[Flag2] = 1
                    )
        )
    )

Right now, it sums the store only if that record has any value where store, month, area exists in the table with Flag2 = 1.

If you actually wanted:

Sum Sales[Value]

Where month is the current row month

and Flag1 = "No"

and the current row store,

and the current row month,

and the current row area

and Flag2 = 1

then:

	
=
VAR _month = Sales[Month]
VAR _area = Sales[Area]
VAR _item = Sales[Item]
VAR _store = Sales[Store]
RETURN
    CALCULATE (
        SUM ( Sales[Value] )
			,Sales[Month] = _Month
            ,[Flag1] = "No"
			
		,FILTER (
            Sales
				Sales[Store] =_store,
                Sales[Month] = _month,
                Sales[Area] = _area,
                Sales[Item] = _item,
                Sales[Flag2] = 1
                    )
        )

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on
Read my blogs on

Remember to spread knowledge in the community when you can!

Re: Get column values from table

Seven440 — Thu, 08 Aug 2019 15:05:36 GMT

SteveCampbell

thanks, the first example seems what I was looking for, but now the error appears:

"The function expects a table expression for argument '2', but a string or numeric expression was used."

If I use your example without condition on Sales[Store], there are no errors. So, as I can judge, the point is about using IN here.

Do you have an idea how to fix it?

Re: Get column values from table

SteveCampbell — Thu, 08 Aug 2019 15:28:54 GMT

yep, made an error - should have been CALCULATETABLE not CALCULATE.

Updated the code, try now

Re: Get column values from table

Seven440 — Mon, 12 Aug 2019 07:07:09 GMT

SteveCampbell

Now the next error appears:

A circular dependency was detected: 'Sales'[Filtered_value],'Sales'[Filtered_value],'Sales'[Filtered_value].

'Sales'[Filtered_value] is the resulting column (where I use the updated formula).

Please help.