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

[Anonymous]

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 ( 'PrimaryMarketTable ODflow'[PrimaryMarket] )
VAR test =
    CALCULATE (
        [O QTY L4wks removed origin city from dest OD flow],
        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
        )
    )
VAR jim =
    LOOKUPVALUE (
        'OD CitytoCityOrigin'[PrimaryMarketDest],
        'OD CitytoCityOrigin'[PrimaryMarketOrigin], origin,
        test
    )
VAR third =
    CALCULATE (
        VALUES ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
        FILTER ( 'OD CitytoCityOrigin', jim <> BLANK () )
    )
VAR secondtake =
    CALCULATE (
        [O QTY L4wks removed origin city from dest OD flow],
        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] IN third
        )
    )
RETURN
    secondtake

So here's what I am trying to do:    Based on a a choice in a slicer of a city name - I want to map this with "Flow Maps" - disregard that unless you have insight into that visualization, if I can get this measure to work I'm all set.  

What my variables are trying to do is build out the information for the flow map - therefore the output should contain the first city and the top 5 locations trucks were sent to, then the data should be filtered to where the main data set "OD CitytoCityOrigin", where the origin city is now filtered to the 5 destination cities.  So the final dataset should be the originally selected city from PrimaryMarketOrigin + the top 5 destinations as PrimaryMarketOrigin.

Variables

Bob = this is just a daterangeid selection - 4 weeks of data, 90 days, or 52 weeks... it works

Origin = Selected value in the slicer (slicer table has no connection to OD CitytoCityOrigin - I am just using this to match up names

Test = this works and shows me the top 5 cities of my origin

jim = this is an attempt to get from Test the list of destination cities - I think it works

third = an attempt to get from the main table the data based on my destination cities in Jim - did not work

Secondtake = another attempt at third

The names above of Third and Secondtake are actually my 30th attempt at least, what I need to do is primarily get my list of Destination cities, and limit the origin data based on the city names matching... this has to be a measure.  Please help.

if you need columns of what I'm doing, you could basically do it with these columns:

OD CitytoCityOrigin
PrimaryMarketOrigin |  PrimaryMarketDest | NumberstoSum | TruckModels

Data:  City_State         |  City_State                | #Numbers#       | JH...

PrimaryMarketTable ODflow

Separate table no relationship:  City_State

Please help my sanity and solve this.  BTW - this formla as written doesn't actually compute because in Secondtake "in third" at the end gives me the error "not a valid table".  - don't fix that, fix everything else, that was just a last ditch effort.

[Anonymous]

Hi @Anonymous ,

Bacause you do not provide a screenshot of the results you are expecting and desensitized example data, I could only according to your descriptions to give some suggestions, please try below dax formula, do some adjustments according to your measure:

O QTY OD FLOW =
VAR bob =
    SELECTEDVALUE ( DateRanges[daterangeid] )
VAR origin =
    SELECTEDVALUE ( 'PrimaryMarketTable ODflow'[PrimaryMarket] )
VAR test =
    CALCULATE (
        [O QTY L4wks removed origin city from dest OD flow],
        TOPN (
            5,
            ALLSELECTED ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
            [O QTY L4wks removed origin city from dest OD flow], DESC
        ),
        VALUES ( 'OD CitytoCityOrigin'[PrimaryMarketdest] ),
        FILTER (
            ALL ( 'OD CitytoCityOrigin' ),
            'OD CitytoCityOrigin'[PrimaryMarketOrigin] = origin
        )
    )
VAR jim =
    LOOKUPVALUE (
        'OD CitytoCityOrigin'[PrimaryMarketDest],
        'OD CitytoCityOrigin'[PrimaryMarketOrigin], origin,
        test
    )
VAR third =
    CALCULATETABLE (
        VALUES ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
        FILTER ( ALL ( 'OD CitytoCityOrigin' ), jim <> BLANK () )
    )
VAR secondtake =
    CALCULATE (
        [O QTY L4wks removed origin city from dest OD flow],
        TOPN (
            5,
            ALLSELECTED ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
            [O QTY L4wks removed origin city from dest OD flow], DESC
        ),
        VALUES ( 'OD CitytoCityOrigin'[PrimaryMarketdest] ),
        FILTER (
            ALL ( 'OD CitytoCityOrigin' ),
            'OD CitytoCityOrigin'[PrimaryMarketOrigin] IN third
        )
    )
RETURN
    secondtake

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@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 · Accepted solution]

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

@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

[Anonymous]

OK - all of this works and gets me close - Var Filterdest returns the cities I want to filter PrimarMarketOrigin for, but that filter is returning nothing.  So Var Destcities returns nothing.

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 (
           10,
            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 = CALCULATEtable(SELECTCOLUMNS('OD CitytoCityOrigin',"PrimaryMarketOrigin",selectedvalue('OD CitytoCityOrigin'[PrimaryMarketDest])),filter(summarizeDestTable,[PrimaryMarketOrigin]<>BLANK()))

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


RETURN
destcities

everything else seems to work...

[Anonymous]

Thank you for your response, but I am still not there.  In your previous post you fixed my query (kind of) - the All ( 'OD CitytoCityOrigin' ) - this messes things up as it ends up grabbing the entire table.  I removed the All and it somewhat works.  

This is where I am at now - This one errors with "A function "PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression."

OK - TEST1 gives me my top 10 cities that we sent trucks to.  I then need to combine that Test1 with those 10 destination cities now as the origin.  

Accroding to Dax Formatter - this query currently has a syntax error, but it isn't caught by power bi, so maybe not.  That syntax error is supposed to be here:

var DestCitiestable = 
    CALCULATEtable ('OD CitytoCityOrigin',
        [O QTY L4wks removed origin city from dest OD flow],  <--- HERE before the comma
     TOPN (
           10,
            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   )
    )

Here is the full code, this does not work.

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 (
           10,
            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 ('OD CitytoCityOrigin',
        [O QTY L4wks removed origin city from dest OD flow],
        TOPN (
           10,
            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 summarizeDestTable = 
ADDCOLUMNS(
    SUMMARIZE(DestCitiestable,'OD CitytoCityOrigin'[PrimaryMarketDest],"PrimaryMarketOrigin",'OD CitytoCityOrigin'[PrimaryMarketDest]),"aColumn",1)


VAR destcities =
    CALCULATE (
        calculate([O QTY L4wks removed origin city from dest OD flow],FILTER (
            'OD CitytoCityOrigin',
            'OD CitytoCityOrigin'[PrimaryMarketOrigin] = DestCitiestable = 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] in summarizeDestTable && 'OD CitytoCityOrigin'[OriginQTY]>0   )
    )


RETURN
destcities

Again, I am not looking for you to fix my syntax, or make this query work - I am hoping you can help me understand how to do what I am trying to do in my two variables "SummarizeDestTable" and "Destcities" - here I am trying to limit my original Test1 query, isolate to the Destination cities (just top 10), and use those dynamically as my new Origin selection to pull another top 5 from those origins.

I would rather you give me the process, than repost my code with slight improvements.

Thanks,

Jonathan