DAX Virtual Table Logic to modify Context

GiovanniCJR · May 21, 2019, 1:08pm

Hi

I would like your help to solve this lack of knowledge.

This case has the objective to consolidate understanding about how to use DAX Virtual Table Logic to modify Context.

If the solution works for Start Date it could be extend to End Date.
So I will just show the Start Date Measures.

MAP:

RULES :

If Selected Cenario <> “Cenario Atual” ; consider “Cenario Atual” for Contratos not affected by selected Cenario and include them at the results.

Medida_Atual_Execpt_Cenario + Medida_Cenario

PROBLEM:

For any reason the calculation doesn´t consider the inclusion of Actual_Except Cenario, when I put the Measure in a Table visual.

Problem

This problem doesn´t appear when using the measure : Q Start Date Total

Just appear when I bring the values by contract : Q Start Date Contract

MODEL:

Model

CONTRACT TABLE :

Contract%20Table

AUXILIARY MEASURES:

Cenário_Selected = SELECTEDVALUE('Dim_Cenário'[Cenário])

Start Date = MIN('PL DAtes'[Date])

Q Start Date = 
VAR Data = [Start Date]
RETURN
    SUMX (
        FILTER ( Contrato; 
               Data >= Contrato[DiniVig] && Data < Contrato[DfimVig] );
       Contrato[Q]
    )

PRINCIPAL MEASURES:

MEASURE WORKING PERFECTLY : Total Aggregation

Q Start Date Total = 

// Data { Start Date/ End Date }    

VAR Data = [Start Date] 

VAR TableAtual = FILTER ( ALL ( Contrato ); Contrato[Cenário] = "Atual" )

VAR Table_Atual_Execpt_Cenario =
EXCEPT (
            SELECTCOLUMNS (
                FILTER ( ALL( Contrato ); Data >= Contrato[DiniVig] && Data < Contrato[DfimVig] );
                "Contrato"; Contrato[Contrato]
            );
            SELECTCOLUMNS (
                FILTER ( VALUES ( Contrato ); Data >= Contrato[DiniVig] && Data < Contrato[DfimVig]);
                "Contrato"; Contrato[Contrato]
            )
        )


VAR Medida_Atual_Execpt_Cenario =
    CALCULATE (
        [Q Start Date];
        TableAtual;
        Table_Atual_Execpt_Cenario
    )


VAR Medida_Cenario = [Q Start Date]


RETURN

 
           Medida_Atual_Execpt_Cenario + Medida_Cenario

MEASURE WORKING WITH ERROR : Contract Aggregation

Q Start Date Contract = 

// Data { Start Date/ End Date }    

VAR Data = [Start Date] 


VAR TableAtual = FILTER( Contrato; Data >= Contrato[DiniVig] && Data < Contrato[DfimVig] && Contrato[Cenário] = "Atual")

VAR Table_Atual_Execpt_Cenario =
EXCEPT(
        SELECTCOLUMNS(
            FILTER( All(Contrato); Data >= Contrato[DiniVig] && Data < Contrato[DfimVig]);
            "Contrato";[Contrato]
        );
        
        SELECTCOLUMNS(
            FILTER(VALUES(Contrato); Data >= Contrato[DiniVig] && Data < Contrato[DfimVig]);
            "Contrato";[Contrato]
        )
     )

VAR Medida_Atual_Execpt_Cenario = 

CALCULATE (
       [Q Start Date];
       TableAtual;
       Table_Atual_Execpt_Cenario  
    )
    
VAR Medida_Cenario =  [Q Start Date]

    
RETURN

 
        Medida_Atual_Execpt_Cenario + Medida_Cenario

If more information is necessary, let me know.

Thanks in Advance !

Best Regards,

Giovanni

sam.mckay · May 23, 2019, 5:16am

Appreciate all the information, but finding it difficult to really understand everything as these almost too much info. I’m struggling to just understand what scenario you are dealing with to be honest right now.

Your formula is very long… there’s really no way I can really understand it as shown without seeing the model and testing it. But in my honest opinion it’s length and complexity is unnecessary and is likely why you’re finding it difficult to get a solution.

As you know I’m not big on large formulas as prefer the ‘measure branching’ technique where you build things out piece by piece.

Have you tried breaking down your formula and seeing how each part behaves in the current context?

Are you actually trying to show through time what contracts are live? and the revenue (Q) you are making over time?

I’m guessing here but for this you need the events in progress pattern. See below.

This pattern allows you to aggregate totals when dealing with multiple dates, and you don’t need multiple date tables like it looks like you maybe have.

I’m also very confused by this part of the formula, as have never come across or had to use anything like this before

EXCEPT (
            SELECTCOLUMNS (
                FILTER ( ALL( Contrato ); Data >= Contrato[DiniVig] && Data < Contrato[DfimVig] );
                "Contrato"; Contrato[Contrato]
            );
            SELECTCOLUMNS (
                FILTER ( VALUES ( Contrato ); Data >= Contrato[DiniVig] && Data < Contrato[DfimVig]);
                "Contrato"; Contrato[Contrato]
            )
        )

I’m hoping that this is just a managing multiple dates problem that can be solved by the patterns I’ve suggested above.

See how you go.

Sam

GiovanniCJR · May 25, 2019, 2:28am

Hi, thanks for your attention.

I did as your sugestion and I believe that I got almost the total result I need.

Just one specific issue remains:

Submarket has a particularity that I have tested and is a cause of error.
And this error, will occur in any other variable that has the same particularity.

When the current scenario changes into the future 2, Contract C changes the submarketing too! (SE to NE)

The way I am using EXCEPT ( the whole tables) is including Contract C in the Current Scenario and Contract C in Future Scenario 2.
Once the Submarkets are different, EXCPT considers these to be Different Contracts. Actually the validity of the contract should consider just the name, scenario and validity ( vigency).

My idea of solution is to consider in Except not the whole table but only the fields:

Contract, Scenario, DiniVig and DfimVig

I Put the files in the DropBox, if you could help.

Best Regards,

Giovanni

sam.mckay · May 27, 2019, 1:40am

I’ve had a look at the model and still a bit confused here just due to a lot of information being on the page and not knowing really where to start

Can you break it down really simply with images exactly where assistance is required.

Just remember I’m coming at this with really no prior understanding of what scenario this is, and what you are trying to achieve (and it’s in a slightly different language).

If you could try to simplify what you need here exactly, either via a formula or modelling help that would be appreciated.

Thanks

GiovanniCJR · May 28, 2019, 2:27am

Hi, thanks for the answer.

Trying to be more clear, I put a new PBix at the Dropbox and an excel file.

They were simplified and translated.

I hope it could help to understand my problem.

I try explain details in the PBIx file too.

Best,

Giovanni

sam.mckay · May 29, 2019, 2:12am

From my initial look at the model you haven’t completed any of the suggestions I made earlier around using the events in progress pattern…any reason why? This is how you manage multiple dates like you have here.

This is how you want your model, with the appropriate inactive relationships.

Then as per the technique mentioned above you want to use the below formula so that is dynamically can calculated up quantity through time.

Contract Quantity (New) = 
CALCULATE( SUM( Contract[Quantity] ),
    FILTER( VALUES( Contract[StartDate] ), Contract[StartDate] <= MAX( Dates[Date] ) ),
    FILTER( VALUES( Contract[EndDate] ), Contract[EndDate] >= MIN( Dates[Date] ) ))

And below for Base

Hopefully this answers what you need. And also you can see how simple this can actually be.

Attached
CASE_Except (1).pbix (186.4 KB)

Sam

GiovanniCJR · May 31, 2019, 10:41pm

Great. Thanks a Lot for the proposed solution.

At first, I did not use the scenario for inactive relationships because I understood that since I should use more than one relationship at the same measure I could not use the Userelationship function and for that reason I was believing that the inactive relationship had no effect .

So, instead, I had kept using the filter without the inactive physical relationships.

Just one more question :

Am I correct to understand that using inactive relationships without the function Userelationship works?

Is that the secret of your solution approach?

Best,

Giovanni

sam.mckay · June 3, 2019, 11:12pm

It can do.

In reality for this specific solution you could actually use no relationship at all and it would still work.

The reason though you have to create these relationships (inactive ones) is because if you do anything else you need to have these and utilise USERELATIONSHIP to turn them on.

If you have active relationship here it will throw everything. So that’s why this is the recommended way to work through this solution

Sam

GiovanniCJR · June 6, 2019, 3:40pm

Ok, Thanks a lot !

Best,

Giovanni