Forum Discussion
creating a measure using if condition based on country
Hi All,
Need help with the below requirement.
I have a requirement where I need to create a new measure based on condition where country = India then X(measure column created on bases different columns) else i need to display Y( measure column based on different logic and column values). As I am coming from SQL background I usually apply IF condition to acheive this. But if i write an IF condition for this its is not taking the dimension country_CD value as a condition.
For X I have created 2 measures based on the formula
KM Travelled Per tons Sold(D) = CALCULATE(
SUM('WC_ULLT_KPI_SUMM_D_F'[TOT_GROSS_WEIGHT_TONS]),
'WC_ULLT_KPI_SUMM_D_F'[SHPMNT_PROCESS_CD]
IN { "ULF.SECONDARY_OUTBOUND_DELIVERY", "ULF.SECONDARY_DIRECT_DISPATCH_DELIVERY" }
)
KM travelled per tons Sold (N) = CALCULATE(
SUM('WC_ULLT_KPI_SUMM_D_F'[TOT_GROSS_WEIGHT_KM_TONS]),
'WC_ULLT_KPI_SUMM_D_F'[SHPMNT_PROCESS_CD]
IN { "ULF.PRIMARY_OUTBOUND_DELIVERY", "ULF.SECONDARY_OUTBOUND_DELIVERY", "ULF.SECONDARY_DIRECT_DISPATCH_DELIVERY" }
)
X=KM travelled per tons Sold (N)/KM Travelled Per tons Sold(D)
And for Y =
calculate(( ((Sum(WC_ULLT_KPI_SUMM_D_F[TRNSPT_WEIGHT_TON]))* Sum(WC_ULLT_KPI_SUMM_D_F[DISTANCE_TRAVELLED_KM]))/Sum(WC_ULLT_KPI_SUMM_D_F[TOT_SALES_TON])/ SUM(WC_ULLT_KPI_SUMM_D_F[NUM_OF_SHIPMENTS]))
Country_CD for country values
I have to create a measure like below
If(Country_CD ='India' then "X" else "Y" END)
Hi Anonymous ,
We can try to use the following measure in the table visual to meet your requirement:
Measure = IF(SELECTEDVALUE(Country[Country_CD])="India",[X],[Y])If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Best regards,
3 Replies
v-lid-msftCommunity Support
Hi Anonymous ,
We can try to use the following measure in the table visual to meet your requirement:
Measure = IF(SELECTEDVALUE(Country[Country_CD])="India",[X],[Y])If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Best regards,Hi All,
Need help with the below requirement.
I have a requirement where I need to create a new measure based on the condition where if country = India then X( is a measure column created already) else I need to display Y( measure column based on different logic and different columns). As I am coming from SQL background I usually apply IF condition to acheive this. But if I am using IF condition for this, its is not allowing me to use the country_CD in the if condition as it is a dimension column. is there any way to acheive this?
below are the DAX caluculations I am using
For X I have created 2 measures based on the formula
KM Travelled Per tons Sold(D) = CALCULATE(SUM('WC_ULLT_KPI_SUMM_D_F'[TOT_GROSS_WEIGHT_TONS]),'WC_ULLT_KPI_SUMM_D_F'[SHPMNT_PROCESS_CD]IN { "ULF.SECONDARY_OUTBOUND_DELIVERY", "ULF.SECONDARY_DIRECT_DISPATCH_DELIVERY" })KM travelled per tons Sold (N) = CALCULATE(SUM('WC_ULLT_KPI_SUMM_D_F'[TOT_GROSS_WEIGHT_KM_TONS]),'WC_ULLT_KPI_SUMM_D_F'[SHPMNT_PROCESS_CD]IN { "ULF.PRIMARY_OUTBOUND_DELIVERY", "ULF.SECONDARY_OUTBOUND_DELIVERY", "ULF.SECONDARY_DIRECT_DISPATCH_DELIVERY" })X=KM travelled per tons Sold (N)/KM Travelled Per tons Sold(D)
And for Y =
calculate(( ((Sum(WC_ULLT_KPI_SUMM_D_F[TRNSPT_WEIGHT_TON]))* Sum(WC_ULLT_KPI_SUMM_D_F[DISTANCE_TRAVELLED_KM]))/Sum(WC_ULLT_KPI_SUMM_D_F[TOT_SALES_TON])/ SUM(WC_ULLT_KPI_SUMM_D_F[NUM_OF_SHIPMENTS]))Country_CD for country valuesI have to create a measure like belowIf(Country_CD ='India' then "X" else "Y" END)