Multiple IF statements in a measure

[Anonymous]

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 ID	Customer Name	Sales	Qty	Cost	SHIP
1	ABC	1000	10	30	XY
2	BCD	2000	30	50	XY
3	XYZ	2000	20	40	CA
4	ABDC	10000	399	100	CA

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

[Ainala]

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

[Anonymous]

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.

[amitchandak]

@Anonymous , Try measure like

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

[Anonymous]

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

[Anonymous]

@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