Forum Discussion

VoijaRisa's avatar
VoijaRisa
Frequent Visitor
2 years ago
Solved

TOPN SUMMARIZE with SWITCH measures

I'm attempting to understand why a measure I've written doesn't work and see if there's something I can do to fix it.

 

Overview of Data & Measures

I'm dealing with shipping data and have a Table visual which has an Origin, Destination, and various numeric measures.

The numeric measures, at their base, are just SUMs of a field in the data, but also do some other tricks:

  • For all, it changes the relationship to the Date table based on a selection from the user (allowing them to filter either by a Ship Date, Invoice Date, or a Processed Date)
  • For currencies, they convert currencies
  • For weights, they convert units

 

Requirement

I am attempting to create Card visuals which displays the SUM of the top 5 rows of this table for the measures described above.

 

My Attempt

As an example, I am using the following measure

 

Top 5 =
SUMX(
    TOPN(
        5,
        SUMMARIZE(
            'Shipments',
            'Shipments'[Origin],
            'Shipments'[Destination],
            "Total Paid", [Total Paid (USD) Sum]),
        [Total Paid], DESC),
    [Total Paid])

 

 

Here's the other measure it's referencing:

 

 

Total Amount Paid (USD) Sum = 
VAR DateSelection = SELECTEDVALUE('Date Slicer Choices'[Date Choice])

VAR TotalPaidInvoiceDate = 
CALCULATE(
    SUM('Shipments'[TOTAL_PAID_USD]), 
    USERELATIONSHIP('Date'[DATE], 'Shipments'[INVOICE_DATE]))

VAR TotalPaidProcessedDate = 
CALCULATE(
    SUM('Shipments'[TOTAL_PAID_USD]), 
    USERELATIONSHIP('Date'[DATE], ' Shipments'[PROCESSED_DATE]))

VAR TotalPaidShipDate = 
CALCULATE(
    SUM('Shipments'[TOTAL_PAID_USD]), 
    USERELATIONSHIP('Date'[DATE], 'Shipments'[SHIP_DATE]))

RETURN
SWITCH(
    TRUE(),
    DateSelection = "Invoice Date", TotalPaidInvoiceDate,
    DateSelection = "Ship Date", TotalPaidShipDate,
    DateSelection = "Processed Date", TotalPaidProcessedDate)

 

 

This measure allows the user to control which of the three date choices. The measure behaves as expected when used in a visual such as the Table visual.

 

In the data model the Date table has three relationships to the Shipment table. The Date[DATE] is joined to Shipment[INVOICE_DATE], Shipment[SHIP_DATE] (the active join), and Shipment[PROCESSED_DATE].

 

My Issue

The Top 5 measure works beautifully when the Date selection is the one that is the active relationship between the Date Table and the Shipment table.

When I select either of the other Date types which are using the inactive relationships, it returns (Blank). I'm quite confused as to why as the subordinate measures (the ones that calculate the Total Paid doing the date SWITCH), both work as expected in the Table visual.

 

Thus, there seems to be some sort of issue passing this second measure into the the first that I'm not understanding.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi VoijaRisa 

     

    Based on your detailed description, I have added some steps to the actions you have already taken, and in my simple tests it is working. I hope the following steps will be helpful to you.

     

    Here's my data model:

     

    1. Create the "Total Amount Paid (USD) Sum" measure in the same way as you used it.

    2. Create separate measures to calculate Top5 for different date selections.

    Top 5 Ship Date = 
SUMX(
    TOPN(
        5,
        ADDCOLUMNS(
            SUMMARIZE(
                'Shipments',
                'Shipments'[Origin],
                'Shipments'[Destination]
            ),
            "Total Paid USD", [Total Amount Paid (USD) Sum]
        ),
        [Total Paid USD], DESC
    ),
    [Total Paid USD]
)

     

    Top 5 Invoice Date = 
CALCULATE(
    SUMX(
        TOPN(
            5,
            ADDCOLUMNS(
                SUMMARIZE(
                    'Shipments',
                    'Shipments'[Origin],
                    'Shipments'[Destination]
                ),
                "Total Paid USD", [Total Amount Paid (USD) Sum]
            ),
            [Total Paid USD], DESC
        ),
        [Total Paid USD]
    ),
    USERELATIONSHIP('Date'[DATE], 'Shipments'[INVOICE_DATE])
)

     

    Top 5 Processed Date = 
CALCULATE(
    SUMX(
        TOPN(
            5,
            ADDCOLUMNS(
                SUMMARIZE(
                    'Shipments',
                    'Shipments'[Origin],
                    'Shipments'[Destination]
                ),
                "Total Paid USD", [Total Amount Paid (USD) Sum]
            ),
            [Total Paid USD], DESC
        ),
        [Total Paid USD]
    ),
    USERELATIONSHIP('Date'[DATE], 'Shipments'[PROCESSED_DATE])
)

     

    3. Combine the Top5 measures created above into one measure.

    Top 5 = 
VAR DateSelection = SELECTEDVALUE('Date Slicer Choices'[Date Choice])

RETURN
SWITCH(
    TRUE(),
    DateSelection = "Invoice Date", [Top 5 Invoice Date],
    DateSelection = "Processed Date", [Top 5 Processed Date],
    DateSelection = "Ship Date", [Top 5 Ship Date]
)

     

    4. Create a card visual with Top 5 measure.

    5. Here is final result:

     

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

     

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I am not sure of how much i can help but i would like to try.  Share the download link of the PBI file.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi VoijaRisa 

     

    Based on your detailed description, I have added some steps to the actions you have already taken, and in my simple tests it is working. I hope the following steps will be helpful to you.

     

    Here's my data model:

     

    1. Create the "Total Amount Paid (USD) Sum" measure in the same way as you used it.

    2. Create separate measures to calculate Top5 for different date selections.

    Top 5 Ship Date = 
SUMX(
    TOPN(
        5,
        ADDCOLUMNS(
            SUMMARIZE(
                'Shipments',
                'Shipments'[Origin],
                'Shipments'[Destination]
            ),
            "Total Paid USD", [Total Amount Paid (USD) Sum]
        ),
        [Total Paid USD], DESC
    ),
    [Total Paid USD]
)

     

    Top 5 Invoice Date = 
CALCULATE(
    SUMX(
        TOPN(
            5,
            ADDCOLUMNS(
                SUMMARIZE(
                    'Shipments',
                    'Shipments'[Origin],
                    'Shipments'[Destination]
                ),
                "Total Paid USD", [Total Amount Paid (USD) Sum]
            ),
            [Total Paid USD], DESC
        ),
        [Total Paid USD]
    ),
    USERELATIONSHIP('Date'[DATE], 'Shipments'[INVOICE_DATE])
)

     

    Top 5 Processed Date = 
CALCULATE(
    SUMX(
        TOPN(
            5,
            ADDCOLUMNS(
                SUMMARIZE(
                    'Shipments',
                    'Shipments'[Origin],
                    'Shipments'[Destination]
                ),
                "Total Paid USD", [Total Amount Paid (USD) Sum]
            ),
            [Total Paid USD], DESC
        ),
        [Total Paid USD]
    ),
    USERELATIONSHIP('Date'[DATE], 'Shipments'[PROCESSED_DATE])
)

     

    3. Combine the Top5 measures created above into one measure.

    Top 5 = 
VAR DateSelection = SELECTEDVALUE('Date Slicer Choices'[Date Choice])

RETURN
SWITCH(
    TRUE(),
    DateSelection = "Invoice Date", [Top 5 Invoice Date],
    DateSelection = "Processed Date", [Top 5 Processed Date],
    DateSelection = "Ship Date", [Top 5 Ship Date]
)

     

    4. Create a card visual with Top 5 measure.

    5. Here is final result:

     

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