Forum Discussion

Seven440's avatar
Seven440
Regular Visitor
7 years ago

Get column values from table

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.

4 Replies

  • SteveCampbell's avatar
    SteveCampbell
    Memorable Member
    7 years ago

    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's avatar
      Seven440
      Regular Visitor
      7 years ago

      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's avatar
        SteveCampbell
        Memorable Member
        7 years ago

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

         

        Updated the code, try now