Forum Discussion

SamuelROS's avatar
SamuelROS
Frequent Visitor
3 years ago
Solved

Inconsistent Measure using Switch

Hello Experts,

I’m trying to replicate the desired output from a working Calculated column into a measure using switch. Although my per line items are getting the desired result, the totals are a little inconsistent.

 

 

 

Calculated Column

 

CC Age = 
SWITCH(
    TRUE(),
    DIM_MRF[Quantity] = DIM_MRF[QuantityDelivered],
    DATEDIFF(
        RELATED( DIM_PO[Date].[Date] ),
        DIM_MRF[DeliveryDate].[Date],
        DAY
    ),
    DIM_MRF[Quantity] <> DIM_MRF[QuantityDelivered],
    DATEDIFF(
        RELATED( DIM_PO[Date].[Date] ),
        TODAY(),
        DAY
    )
)

 

 

Measure

 

M Age = 
MAXX('DIM_MRF',
    SWITCH(
        TRUE(),
        SELECTEDVALUE(DIM_MRF[Quantity]) = DIM_MRF[QuantityDelivered],
            DATEDIFF(
                RELATED(DIM_PO[Date].[Date]),
                DIM_MRF[DeliveryDate].[Date],DAY
            ),
        SELECTEDVALUE(DIM_MRF[Quantity]) <> DIM_MRF[QuantityDelivered],
            DATEDIFF(
                RELATED(DIM_PO[Date].[Date]),
                TODAY(),DAY
            )
    )
)

 

 

 

MRFNumberPONumberMaterialNumberPOItemNumberQuantityQuantityDeliveredPOUnitPriceDeliveryDate
IST-22-52PO-00001IST-0013115542217/11/2022
IST-22-52PO-00001IST-0013555 12 
IST-22-52PO-00001IST-0013662 3380 
IST-22-52PO-00001IST-0013772 608 
IST-22-52PO-00001IST-0013882 172 
IST-22-52PO-00001IST-0013992 708 
IST-22-52PO-00001IST-00140102 134 
IST-22-52PO-00001IST-001411155114617/11/2022
IST-22-46PO-00002IST-00104120208215/11/2022
IST-22-46PO-00002IST-001023101055615/11/2022
IST-22-46PO-00002IST-001014555015/11/2022
IST-22-46PO-00002IST-0010051174015/11/2022
IST-22-46PO-00002IST-000996151161.8615/11/2022
IST-22-46PO-00002IST-00098722126025/11/2022
IST-22-46PO-00002IST-000941155915/11/2022
IST-22-46PO-00002IST-00091141 95.22 
IST-22-41PO-00003IST-00158155426.516/11/2023
IST-22-41PO-00003IST-00159244358.2616/11/2023
IST-22-41PO-00003IST-00160311307.0816/11/2023
IST-22-41PO-00003IST-00161444196.1916/11/2023
IST-23-81PO-00004IST-2023-46816 5385.61 
IST-23-81PO-00004IST-2023-46726 1520 
IST-23-55PO-00005IST-2023-54723 1265 
IST-23-55PO-00005IST-2023-54836 255 
IST-23-55PO-00005IST-2023-54946 255 
IST-23-55PO-00005IST-2023-55053 198 
IST-23-55PO-00005IST-2023-55382 1848.75 
IST-23-55PO-00005IST-2023-555101 2985 
IST-23-55PO-00005IST-2023-54613 5575 
IST-23-74PO-00006IST-0026922 13.48 
IST-23-74PO-00006IST-00268340 12.52 
IST-23-74PO-00006IST-0026712 30 

 

DescriptionPONumberDateCurrencyVendor
Description 01PO-0000116/08/2022USDVendor 1
Description 02PO-0000227/09/2022EURVendor 2
Description 03PO-0000309/05/2023EURVendor 2
Description 04PO-0000405/06/2023EURVendor 2
Description 05PO-0000504/07/2023EURVendor 2
Description 06PO-0000627/09/2022USDVendor 1

 

 

 

 

 

Need Help
  • Chris_White's avatar
    Chris_White
    3 years ago

    Remove the SELECTEDVALUE function from your measure.  MAXX is already converting to row context so having SELECTEDVALUE in there is tripping it up.

    M Age =
    MAXX('DIM_MRF',
        SWITCH(
            TRUE(),
            DIM_MRF[Quantity] = DIM_MRF[QuantityDelivered],
                DATEDIFF(
                    RELATED(DIM_PO[Date].[Date]),
                    DIM_MRF[DeliveryDate].[Date],DAY
                ),
            DIM_MRF[Quantity] <> DIM_MRF[QuantityDelivered],
                DATEDIFF(
                    RELATED(DIM_PO[Date].[Date]),
                    TODAY(),DAY
                )
        )
    )

2 Replies

Replies have been turned off for this discussion
  • Chris_White's avatar
    Chris_White
    Resolver II
    3 years ago

    Remove the SELECTEDVALUE function from your measure.  MAXX is already converting to row context so having SELECTEDVALUE in there is tripping it up.

    M Age =
    MAXX('DIM_MRF',
        SWITCH(
            TRUE(),
            DIM_MRF[Quantity] = DIM_MRF[QuantityDelivered],
                DATEDIFF(
                    RELATED(DIM_PO[Date].[Date]),
                    DIM_MRF[DeliveryDate].[Date],DAY
                ),
            DIM_MRF[Quantity] <> DIM_MRF[QuantityDelivered],
                DATEDIFF(
                    RELATED(DIM_PO[Date].[Date]),
                    TODAY(),DAY
                )
        )
    )