Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Multiple IF statements in a measure

Hello Power Users,

I'm having some issues while creating a DAX measure. I want to create a measure with few conditions and calculate it. Below is my data look like. MY source is SSAS tabular model so can not create a calculated column in PBI.

 

Customer IDCustomer NameSalesQtyCostSHIP
1ABC10001030XY
2BCD20003050XY
3XYZ20002040CA
4ABDC10000399100CA

 

I have created some measures Sales YTD, QTY YTD, COST YTD's and i would like to create another measure by using QTY YTD. 

If SHIP="XY" then QTY YTD, IF SHIP="CA" then QTYYTD * COST YTD  other wise 0. 

 

I have create a measure like below.

Measure = SUMX(SWITCH(TRUE(), SHIP="XY", QTYYTD,
SHIP="CA", QTYYTD*COSTYTD,0)

This is giving me 0 or sometime memory exception issue.

 

How can i achieve this.

 

Thanks

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Try measure like

     

    Measure = SUMX(table, SWITCH(TRUE(), [SHIP]="XY", [QTYYTD], [SHIP]="CA", [QTYYTD]*[COSTYTD],0) )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak Thanks for the response. That is the measure i tried but i'm getting memory exception issue. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        amitchandak Anonymous I have changed the dataload to Import mode and loaded only 2 year of data and i'm not getting memory exception issues. the measure seems right but the condition is not right because only one condition is working otherone is not working.  When i select XY its working little bit right(some rows its not matching) and when i select CA it is giving me blank. Please help

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    It usually occurs when your computer doesn't have enough memory space for Power BI to run. Close other unnecessary running applications/softwares will spare more memory for Power BI if this error just appear occasionally. 

     

    Also, if your table is in Import mode and you import a large amount of data into the model, it will consume much memory. You can use a small sample data when creating a report in Power BI Desktop. Then use query parameter to switch data source dynamically. After you complete the report and publish it to the service, you can change the query parameter to use the actual data source which has large data. See reference: Change the Source of Power BI Datasets Dynamically Using Power Query Parameters - RADACAD

     

    Try to increase the Maximum allowed to 16000 under File / Options and Settings / Options / Global / Data Load.  Data Cache Management Options.

     

    Refer to:

    https://windowsreport.com/memory-error-allocation-failure/

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ainala's avatar
    Ainala
    Frequent Visitor
    4 years ago

    Jihwan_Kim , I tried helping with this solution but i'm not able to do it. Perhaps you can help.