creating a measure using if condition based on country

[Anonymous]

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)

[Anonymous]

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 values

 I have to create a measure like below 

If(Country_CD ='India' then "X" else "Y" END)

[v-lid-msft · Accepted solution]

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,

[Anonymous]

Thank you so much! I missed SELECTEDVALUE function all the time.

Regards,

Rakesh