Get column values from table

[Seven440]

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.

[SteveCampbell]

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! 

[Seven440]

@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?

[SteveCampbell]

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

Updated the code, try now

[Seven440]

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