none
Variable in a Dax formula to impact cubeset formula RRS feed

  • Question

  • I have a data model where I have the dax formula as below:

    Adjusted_Sales:= CALCULATE([Sales],Data_Table[Company]<>"ABC",Data_Table[Company]<>"DEF",Data_Table[Eligibility]="Eligible",filter(Data_Table,Data_Table[FC Status]="Not in FC" || Data_Table[FC Status]="Not in Plan" || Data_Table[FC Status]="Future"),Data_Table[Fiscal_Period]="Q1_2017",Data_Table[REGION]="EUROPE")

    I would like to make the last section in the formula : Data_Table[REGION]="EUROPE" become variable updated by the values on the Slicer for REGION where it might be EUROPE, ASIA or AMERICA as selected on the slicer. This seems like it can be accomplished if I were to pull the measures on a pivot table.  I am also creating a ranking based on the Adjusted_Sales measure as follows:

    RANK_Adjusted_Sales:=RANKX(ALLSELECTED(Data_Table[Clients]),[Adjusted_Sales]) and in excel I write a cubeset formula for this ranking: CUBESET("ThisWorkbookDataModel","[Data_Table].[Clients].Children","Clients_Ranked",1,"[Measures].[Rank_Adjusted_Sales]").

    What I am struggling with is, if the Data_Table[REGION]="EUROPE" criteria in the Adjusted_Sales measure is static, then the cubeset  calculates based on that measure. However I want the cubeset change as the Data_Table[REGION] becomes variable influenced by the slicer selection.

    Is it possible of passing a variable to DAX formula to impact a cubeset or it is not doable in powerpivot?

    Thank you / Steve

    Tuesday, March 14, 2017 2:15 PM

Answers

  • Hi SteveSTez,

    If you create a slicer, and relate the slicer to Pivot Table, Table and measure will change automatically different value in slicer. So you do not need to add a filter Data_Table[REGION]="EUROPE" in your measure.

    In addition, you'd better add a filter function including all your conditions as follows.

    Adjusted_Sales:= CALCULATE([Sales],FILTER(Data_Table, Data_Table[Company]<>"ABC",Data_Table[Company]<>"DEF",Data_Table[Eligibility]="Eligible",filter(Data_Table,Data_Table[FC Status]="Not in FC" || Data_Table[FC Status]="Not in Plan" || Data_Table[FC Status]="Future"),Data_Table[Fiscal_Period]="Q1_2017"))

    If this is not what you want, please share your sample data and list expected result. For more details about creating variable, please refer to this article.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 15, 2017 2:38 AM
    Moderator