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

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's avatar
Anonymous
Not applicable
3 years ago

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...