Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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!
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
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?
yep, made an error - should have been CALCULATETABLE not CALCULATE.
Updated the code, try now
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |