Treatas not working as it should, data lineage issue? - what am I missing?

[Anonymous]

OK - this is a continuation of another thread (link at bottom) - The other thread has been too obvuscated with my errors and responses that were only so-so helpful. I think I am 98% there but this still is not working.

Here are some screenshots - 

On the map if I click "Show as a table" you can see my measure correctly contains the Destination Cities (PrimaryMarketDest)

Destination cities and Origin cities are derived from the same content - there is a 1:1 exact match on spelling and format.

Here is my query - the above pictures are based on the query returning the variable "Filterdest" - as in "give me my final destination filters."

The issue has to do with data lineage I think but I thought I got over that entirely by utilizing the Treatas function...  The treatas function did nothing, Destcities variable returns nothing, zip zero nada.  Everything else works just fine.

O QTY L4wks OD FLOW =
VAR Daterange =
    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 filterdest =
    SELECTCOLUMNS ( DestCitiestable, "PrimaryMarketOrigin", [PrimaryMarketDest] )
VAR finalfilter =
    TREATAS ( filterdest, 'OD CitytoCityOrigin'[PrimaryMarketOrigin] )
VAR odcalc =
    CALCULATE (
        SUM ( 'OD CitytoCityOrigin'[OriginQty] ),
        ALLEXCEPT ( Equipment, Equipment[ProductCategory], Equipment[ProductModel] ),
        FILTER (
            'OD CitytoCityOrigin',
            'OD CitytoCityOrigin'[DateRangeID] = Daterange
                && 'OD CitytoCityOrigin'[PrimaryMarketOrigin] IN finalfilter
        )
    )
VAR destcities =
    CALCULATE (
        CALCULATE (
            odcalc,
            FILTER (
                'OD CitytoCityOrigin',
                'OD CitytoCityOrigin'[PrimaryMarketOrigin]
                    IN finalfilter
                    && 'OD CitytoCityOrigin'[OriginQTY] > 0
            )
        ),
        TOPN (
            5,
            ALLSELECTED ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
            odcalc, DESC
        ),
        VALUES ( 'OD CitytoCityOrigin'[PrimaryMarketdest] ),
        FILTER (
            'OD CitytoCityOrigin',
            'OD CitytoCityOrigin'[PrimaryMarketOrigin]
                IN finalfilter
                && 'OD CitytoCityOrigin'[OriginQTY] > 0
        )
    )
RETURN
    destcities

What this measure should return is the below data - (Only without the teal lines)

As you can see in the DAX, Destcities is pretty much the same as Test1.  Here is Test1 correctly showing us the output for the ONE  selected PrimaryMarketOrigin:

What am I missing?  I need to rerun test1 (this is called destcities), but I want the input of PrimaryMarketOrigin to be the destination cities from Test1...  how can I do this? what do I have wrong?

Thank you @Greg_Deckler for replying to my previous post and helping me get to this point.
Thanks for any time taken to fix my sanity!

One last note - the measure referenced in Test1 "[O QTY L4wks removed origin city from dest OD flow]"  -- this is the same as the variable "odcalc" - but the long-named measure actually references the selected value in the PrimaryMarket slicer, ODcalc variable removes this as it is done in the calculate around odcalc.

@amitchandak @FreemanZ @Greg_Deckler @Mikelytics @lbendlin @V-pazhen-msft @sudhav @vijay273162 @PaulDBrown @bcdobbs 

Old thread:  Re: How do I do this? Variable table, limiting da... - Microsoft Power BI Community

[tamerj1 · Accepted solution]

Hello @Anonymous 
Please refer to attached sample file for more details about the proposed solution. The sample file does not contain the complete data model therefore, you some items won't exist in the proposed dax inside the file. You can find two versions of the measure but I guess you're looking for version2.

O QTY L4wks removed origin city from dest OD flow =
VAR CurrentOrigin =
    SELECTEDVALUE ( 'OD CitytoCityOrigin'[PrimaryMarketOrigin] )
VAR bob =
    SELECTEDVALUE ( DateRanges[daterangeid] )
RETURN
    CALCULATE (
        SUM ( 'OD CitytoCityOrigin'[OriginQty] ),
        ALLEXCEPT ( Equipment, Equipment[ProductCategory], Equipment[ProductModel] ),
        KEEPFILTERS ( 'OD CitytoCityOrigin'[DateRangeID] = bob ),
        KEEPFILTERS ( 'OD CitytoCityOrigin'[PrimaryMarketDest] <> CurrentOrigin )
    )

O QTY L4wks OD FLOW 2 =
VAR Origin =
    VALUES ( 'PrimaryMarketTable ODflow'[PrimaryMarket] )
VAR CurrentOrigin =
    SELECTEDVALUE ( 'OD CitytoCityOrigin'[PrimaryMarketOrigin] )
VAR OriginFilter =
    UNION (
        TOPN (
            3,
            CALCULATETABLE (
                VALUES ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
                'OD CitytoCityOrigin'[PrimaryMarketOrigin] = Origin,
                ALL ( 'OD CitytoCityOrigin' )
            ),
            CALCULATE (
                [O QTY L4wks removed origin city from dest OD flow],
                'OD CitytoCityOrigin'[PrimaryMarketOrigin] = Origin
            ), DESC
        ),
        Origin
    )
VAR DestFilter1 =
    TOPN (
        3,
        FILTER (
            ALLSELECTED ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
            NOT ( 'OD CitytoCityOrigin'[PrimaryMarketDest] IN Origin )
        ),
        [O QTY L4wks removed origin city from dest OD flow], DESC
    )
VAR DestFilter2 =
    TOPN (
        3,
        FILTER (
            ALLSELECTED ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
            NOT ( 'OD CitytoCityOrigin'[PrimaryMarketDest] IN OriginFilter )
        ),
        [O QTY L4wks removed origin city from dest OD flow], DESC
    )
VAR Result1 =
    CALCULATE (
        [O QTY L4wks removed origin city from dest OD flow],
        KEEPFILTERS ( 'OD CitytoCityOrigin'[PrimaryMarketOrigin] IN OriginFilter ),
        KEEPFILTERS ( 'OD CitytoCityOrigin'[PrimaryMarketDest] IN DestFilter1 )
    )
VAR Result2 =
    CALCULATE (
        [O QTY L4wks removed origin city from dest OD flow],
        KEEPFILTERS ( 'OD CitytoCityOrigin'[PrimaryMarketOrigin] IN OriginFilter ),
        KEEPFILTERS ( 'OD CitytoCityOrigin'[PrimaryMarketDest] IN DestFilter2 )
    )
RETURN
    IF ( CurrentOrigin = Origin, Result1, Result2 )

[Anonymous]

Holy Hell thank you so much!  this worked after I tweeked it a bit.  

I'm now trying to get a new layer in - that is another level of destinations as origin... 
A:  Truck leaves from a specific City and goes to 10 cities - I map those 10 -- this is Result1
B:  One of those cities - I map the top 3 cities that city sent to -- This is Result2
C:  NEEDED - how do I get the cities that are destinations of Result2 to map?

This is still the main crux of my issue - if it worked how I was trying to do it I could do this, but I don't entirely understand in what you did, WHY Result2 even works 🙂  I'm happy it does though.

Since I can't figure out why Result2 works, I don't know how to make a result3... I started trying, but I'm doing it the same way I was before and that's not working and I don't know why.

This is the Code that correctly works for my setup - I had to change a few things you had done, but it works as desired now.

O QTY OD FLOW =
--ODFlowCalc = 
--var daterange = SELECTEDVALUE(DateDim[DateRangeID])
--var origin = selectedvalue('PrimaryMarketTable ODflow'[PrimaryMarket])
--return calculate(Sum('OD CitytoCityOrigin'[OriginQty]),allexcept(Equipment,Equipment[ProductCategory],Equipment[ProductModel]),filter('OD CitytoCityOrigin','OD CitytoCityOrigin'[DateRangeID]=Daterange))
VAR origindests =
    SELECTEDVALUE ( FilterOrigin[DestinationQTY] )
VAR destdests =
    SELECTEDVALUE ( FilterDest[DestQTY] )
VAR Origin =
    VALUES ( 'PrimaryMarketTable ODflow'[PrimaryMarket] )
VAR CurrentOrigin =
    SELECTEDVALUE ( 'OD CitytoCityOrigin'[PrimaryMarketOrigin] )
VAR OriginFilter =
    UNION (
        TOPN (
            origindests,
            CALCULATETABLE (
                VALUES ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
                'OD CitytoCityOrigin'[PrimaryMarketOrigin] = Origin,
                ALL ( 'OD CitytoCityOrigin' )
            ),
            CALCULATE ( [ODFlowCalc], 'OD CitytoCityOrigin'[PrimaryMarketOrigin] = Origin ), DESC
        ),
        Origin
    )
VAR DestFilter1 =
    TOPN (
        origindests,
        FILTER (
            ALLSELECTED ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
            NOT ( 'OD CitytoCityOrigin'[PrimaryMarketDest] IN Origin )
        ),
        [ODFlowCalc], DESC
    )
VAR Result1 =
    CALCULATE (
        [ODFlowCalc],
        KEEPFILTERS ( 'OD CitytoCityOrigin'[PrimaryMarketOrigin] IN OriginFilter ),
        KEEPFILTERS ( 'OD CitytoCityOrigin'[PrimaryMarketDest] IN DestFilter1 )
    )
VAR DestFilter2 =
    CALCULATETABLE (
        TOPN (
            destdests,
            FILTER (
                ALLSELECTED ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
                'OD CitytoCityOrigin'[PrimaryMarketdest] IN OriginFilter
            ),
            [ODFlowCalc], DESC
        ),
        FILTER (
            ALLSELECTED ( 'OD CitytoCityOrigin'[PrimaryMarketOrigin] ),
            'OD CitytoCityOrigin'[PrimaryMarketOrigin] IN OriginFilter
        )
    )
VAR DestFilter3 =
    TOPN (
        destdests,
        ALLSELECTED ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
        [ODFlowCalc], DESC
    )
VAR Result2 =
    CALCULATE (
        [ODFlowCalc],
        KEEPFILTERS ( 'OD CitytoCityOrigin'[PrimaryMarketOrigin] IN OriginFilter ),
        KEEPFILTERS ( 'OD CitytoCityOrigin'[PrimaryMarketdest] IN DestFilter3 )
    )
VAR OriginFilter2 =
    UNION (
        TOPN (
            origindests,
            CALCULATETABLE (
                VALUES ( 'OD CitytoCityOrigin'[PrimaryMarketDest] ),
                FILTER ( 'OD CitytoCityOrigin', result2 <> BLANK () ),
                --'OD CitytoCityOrigin'[PrimaryMarketOrigin] = Origin,
                ALL ( 'OD CitytoCityOrigin' )
            ),
            CALCULATE (
                [ODFlowCalc],
                FILTER (
                    'OD CitytoCityOrigin',
                    result2 <> BLANK ()
                ) --'OD CitytoCityOrigin'[PrimaryMarketOrigin] = Origin
            ), DESC
        ),
        Origin
    )
VAR Result3 =
    CALCULATE (
        [ODFlowCalc],
        KEEPFILTERS ( 'OD CitytoCityOrigin'[PrimaryMarketOrigin] IN OriginFilter2 ),
        KEEPFILTERS ( 'OD CitytoCityOrigin'[PrimaryMarketdest] IN DestFilter3 )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'PrimaryMarketTable ODflow'[PrimaryMarket] ) = CurrentOrigin,
        Result1,
        Result2
    )

BTW - here's the beautiful map you helped create for Dallas... although the true beauty of this analysis are the calculations I will be able to make off of the table this is also generating!

[tamerj1]

Hi @Anonymous 
Any chance you can share a sample file from yourside? Your version of the measure did not filter the table in my sample file.

[Anonymous]

how do I attach a file?  I have uploaded it to my onedrive and shared the folder here:
https://1drv.ms/u/s!ArhoIVw8JI_Jgr0JBWkSJfS-CQSXLg?e=HfVM5l

As you will see it's pretty basic - I think I have captured everything in my query, the only thing you're missing is the ODFlowCalc data - just sum OriginQty I've already filtered to the rest:

return calculate(Sum('OD CitytoCityOrigin'[OriginQty])

Hope this helps - this should give you a spider out from Dallas (result1), and a spider out from Lubbock Texas (result2)... oh I guess I should add a result3 in...  I'll get that saved up shortly!
--,allexcept(Equipment,Equipment[ProductCategory],Equipment[ProductModel]),filter('OD CitytoCityOrigin','OD CitytoCityOrigin'[DateRangeID]=Daterange)

)

[Anonymous]

OK - I replaced my Sample.xlsx file in that folder.  If you can get Result3 to work, you will see Orlando branching out when you select Dallas and include 10 destinations as your Primary (result1), just need to include 3 destinations for result2 and Orlando should branch.

That data is a very small sub-set but should work.

@tamerj1  I guess Result3 is pretty complicated.  Since Result 1 and 2 are saying "give me all places these locations sent trucks to and just show the top 5," result 3 runs into issues where the second destination city sent to one of the earlier destination (result2) or primary Origin (result1).  So in those situations result 3 would NOT want to calculate if the city was one previously from Result1 or 2.  

To make that more clear - Dallas sends to Houston, Dallas is mapped - Result1 and Houston is mapped - result2.  Well Houston sends to Dallas for a potential mapping of Dallas in result3, but this has already been accomplished in result1 so we would not want to calculate again.

[tamerj1]

@Anonymous 
I had a little bit different understanding of the requirement. I thought that the origins mapped in Result1 must be excluded from the destinations of Result2 even if some of them are among the top(n). But I guess this way we will be run out of cities after Result2. In other words if city A is selected then Result1 should filter the top 3 destinations other than A; let's say B, C & D an those shall be become (Along with A) the origins of Result2 which has to MAP the top 3 destinations other than A, B, C & D.

That means origins decide which destinations and destinations some how decide which origins. This type of circular dependency or recursive calculation, if you would, is extremely complicated to handle with DAX and this is the reason for the long code of verion2 of the measure, otherwise the much more simplified verion1 would do the job but the map would then look more like a fabric that a web.

As you said, Result3, if possible would be extremely complicated, at least following the same methodology.

Other method by unitizing a filter measure placed in the filter pane of the visual while keeping the simple SUM measure in the values of the visual, would also be possible, not sure if it would be less complex, but worth a try.

Thank you so much for sharing this problem which opens a new door for learning DAX and PowerBi and I think I will dive deeper into this when I have the time. Please let me know if you still need any further assistance on this or any other subject.

Have a nice weekend and a happy new year!

[Anonymous]

I think you may have it more complicated, let me talk through it with specifics:

You pick an origin city - Dallas.  Dallas' top 5 destinations then show that Dallas sent X number of trucks to each of them.  Dallas also sends trucks to Dallas - this is what should be excluded.  Dallas sends to Houston - at Houston we want to know what 5 top cities they send trucks to (including Dallas).  Houston also sends trucks to (let's say) Miami (fictional data).  In Result 3 we want to know what 5 locations Miami sends to.   In the end what this will give us is the following:  If a truck is sent from Dallas to Houston, it has this probability of ending up in Miami... From Miami it has this probability of ending up in one of Miami's destinations.

So what I was trying to do (which I think you did the same thing just made it work), is just recycle the previously calculated destinations as the new "origin" requirement.  So Result 3 would just need a way to take Result2's destination cities and apply them as the new origin, then rerun the same calculation taken in Result 1 and result 2.  

Also if Houston sends to Dallas we want to know that, Dallas should be listed as a "primaryMarketdest" with QTY... but when the input is given to result 3 to calculate, it should not include Dallas.  This one just seems easy to work out....   

What I can't figure out is why my filters didn't work from my original query... that's what we need to get the Dax people working on :).

thanks,

Jonathan 

[tamerj1]

Hi @Anonymous 

would you please provide a clear screenshot of your data model?

[Anonymous]

It's a single table, very simple. 

The way it works is there are 20mln rows in [OD CitytoCityOrigin], ALL city to city combinations where a truck was sent.  Filter PrimaryMarketOrigin to one location and you see all the places that city sent a truck and how many were sent.  Filter PrimaryMarketDest to one city and you see all the cities sending trucks to that location.

I want to sum the originqty for one PrimaryMarketOrigin, and show (map) just the top 5.  Then I want to add on to that those top 5 locations, I want to see their top 5 locations they sent trucks.  Therefore my DAX above should be getting a list of my top 5 PrimaryMarketDest for the ONE selected PrimaryMarketOrigin, then showing me the top 5 locations those top 5 PrimaryMarketDest sent their trucks.  So the top 5 PrimaryMarketDests should then be used as PrimaryMarketOrigin and show me their top 5 -- THIS IS WHAT DESTCITIES IS TRYING TO DO.  In the end I think I can just Return Test1 + Destcities and I'll have my full view.  But Destcities won't work.

OD CitytoCityOrigin
PrimaryMarketOrigin |  PrimaryMarketDest | OriginQty          | TruckModels  | DateRangeID

Data:  City_State         |  City_State                | #Numbers#       | productdim   | 10

Then a second table with no connection that locks in the PrimaryMarketOrigin used in Test1 - this filter is used in the calculate in Test1 in my DAX in my original post.
PrimaryMarketTable ODflow

Here is the measure from Test1 that references that table (PrimaryMarketTable ODflow), and it's also referenced in the above dax:

O QTY L4wks removed origin city from dest OD flow =

var origin = SELECTEDVALUE('PrimaryMarketTable ODflow'[PrimaryMarket])

var bob = SELECTEDVALUE(DateRanges[daterangeid])

return calculate(sum('OD CitytoCityOrigin'[OriginQty]),allexcept(Equipment,Equipment[ProductCategory],Equipment[ProductModel]),filter('OD CitytoCityOrigin','OD CitytoCityOrigin'[DateRangeID]=bob&&'OD CitytoCityOrigin'[PrimaryMarketDest]<>origin))

[tamerj1]

@Anonymous 

Wow! That is beautiful 😍 

I'll have a look at it tomorrow. I'll let you know if I was able to get anywhere with it.