+ Reply to Thread
Results 1 to 1 of 1

DAX , Filter using values from table column

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    804

    DAX , Filter using values from table column

    Hi,

    I have posted, almost the same question on Mr Excel ;
    https://www.mrexcel.com/board/thread...table.1252088/

    I would like to filter a table down twice and see the "Item" values that are in both, in power query which I have attacched;

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        Blue = Table.SelectRows(Source , each ([Color] = "Blue")),
        Green = Table.SelectRows(  Source , each List.Contains( {"Green"},[Color] ) ), 
        Custom1 = Table.SelectRows(  Green, each  List.ContainsAny( Blue[Item] , {[Item] } ) )
    in
        Custom1
    But I have been trying to do this in DAX but have two problems,
    I can get what I want by filtering and then taking the values and hardcoding them in so A, B and C values returned in 'atable' ;

    EVALUATE
    VAR atable =
        CALCULATETABLE ( FILTER ( ALL ( TableA ), TableA[Color] = "Green" ) )
    VAR btable =
        CALCULATETABLE (
            SUMMARIZE ( TableA, TableA[Color], TableA[Item] ),
            TableA[Color] = "Blue"
        )
    VAR lookupV =
        SUMMARIZE ( btable, [Item] ) //not used in return 
    VAR anser1 =
        FILTER ( atable, [Item] = "D" || [Item] = "C" )
    RETURN
        anser1

    But what I am trying for is someting along the lines of ;

      CALCULATETABLE( atable , [Item]  IN  lookupV )

    I think that SUMMARIZE( btable,[Item] ) would give same as { "D","C"}
    I have go a result using Filter and Or ||


    I have attached my starting point, what I've done so far has been in studio rather than on file .


    Richard
    Attached Files Attached Files
    Last edited by Dicken; 01-18-2024 at 01:31 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Filter multiple values using if statement from table column
    By jaryszek in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-14-2021, 06:53 AM
  2. Replies: 12
    Last Post: 09-23-2019, 04:30 PM
  3. Replies: 8
    Last Post: 06-02-2015, 01:51 PM
  4. Replies: 6
    Last Post: 10-16-2014, 08:42 AM
  5. How to filter a column in a table based on a set of values in another column
    By redhawk87 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2013, 06:24 PM
  6. Replies: 1
    Last Post: 01-14-2013, 04:01 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1