Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How do I do this? Variable table, limiting data based on selection

I can't get this measure to work... I'm half way maybe.  Here's my code:   O QTY OD FLOW = VAR bob = SELECTEDVALUE ( DateRanges[daterangeid] ) VAR origin = SELECTEDVALUE ( 'PrimaryMarketTa...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous With regard to your question above you can store a variable and use it as a filter in another table like:

    Measure =
  VAR __table = SELECTCOLUMNS('Table',"FilterColumn",[Column])
  VAR __filteredTable = FILTER('Table2',[Some Column] IN __table)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

amitchandak  or Greg_Deckler want to take a shot at this?  Just please tell me how to store a variable, then use that table of multiple values as a filter in another variable.  It can be done right?

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Anonymous With regard to your question above you can store a variable and use it as a filter in another table like:

Measure =
  VAR __table = SELECTCOLUMNS('Table',"FilterColumn",[Column])
  VAR __filteredTable = FILTER('Table2',[Some Column] IN __table)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Greg_Deckler can you help me understand why my current query provides me no data in response?  I've implemented what you said, but it's still not working.

    Here's what I'm thinking I need to "get around" - that everything in my FilterDest variable is really related to a single PrimaryMarketOrigin - therefore when I say in Destcities "look up everything where PrimaryMarketOrigin in Filteredest" - what is actually happening is DAX is still lining up PrimaryMarketOrigin to PrimaryMarketOrigin - LMK if this is incorrect and that by renaming that column in Filterdest (here I rename PrimaryMarketDest to PrimaryMarketOrigin) - that by doing that I have forced PowerBI to agree that column is PrimaryMarketOrigin?

    Here's my code - everything works except the last Var... in the end my Return will be "Return Test1+Destcities" - but I can't get Destcities to work.  Let me know if you need me to mock this up in a powerbi... I can't share my file with you however.

     

    O QTY L4wks OD FLOW = 


VAR bob =
    SELECTEDVALUE ( DateRanges[daterangeid] )
VAR origin =
    SELECTEDVALUE ( 'PrimaryMarketTable ODflow'[PrimaryMarket] )

VAR test1 =
    CALCULATE (
        calculate([O QTY L4wks removed origin city from dest OD flow],FILTER (
            'OD CitytoCityOrigin' ,
            'OD CitytoCityOrigin'[PrimaryMarketOrigin] = origin && 'OD CitytoCityOrigin'[OriginQTY]>0
        )),
        TOPN (
           5,
            ALLSELECTED ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
            [O QTY L4wks removed origin city from dest OD flow], DESC
        ),
        VALUES ( 'OD CitytoCityOrigin'[PrimaryMarketdest] ),
        FILTER (
         'OD CitytoCityOrigin' ,
            'OD CitytoCityOrigin'[PrimaryMarketOrigin] = origin && 'OD CitytoCityOrigin'[OriginQTY]>0   )
    )


var DestCitiestable = 
    CALCULATEtable (values('OD CitytoCityOrigin'[PrimaryMarketDest]),
        FILTER (
         'OD CitytoCityOrigin' ,
            test1<>BLANK())
    )

var summarizeDestTable = 
ADDCOLUMNS(
    SUMMARIZE(DestCitiestable,'OD CitytoCityOrigin'[PrimaryMarketDest],"PrimaryMarketOrigin",'OD CitytoCityOrigin'[PrimaryMarketDest]),"aColumn",1)

var filterdest = SELECTCOLUMNS(summarizeDestTable,"PrimaryMarketOrigin",[PrimaryMarketDest])



VAR destcities =

        calculate(
           calculate(sum('OD CitytoCityOrigin'[OriginQty]),allexcept(Equipment,Equipment[ProductCategory],Equipment[ProductModel]),filter('OD CitytoCityOrigin','OD CitytoCityOrigin'[DateRangeID]=bob&&'OD CitytoCityOrigin'[PrimaryMarketOrigin] in filterdest)),
        TOPN (
           5,
            ALLSELECTED ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
          calculate(sum('OD CitytoCityOrigin'[OriginQty]),allexcept(Equipment,Equipment[ProductCategory],Equipment[ProductModel]),filter('OD CitytoCityOrigin','OD CitytoCityOrigin'[DateRangeID]=bob&&'OD CitytoCityOrigin'[PrimaryMarketOrigin] in filterdest)), DESC
        ))


RETURN
destcities