Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
monojchakrab
Resolver III
Resolver III

Top 10 cities basis values chosen on slicers

Hi good people,

 

I am working with a fact table as below :

 

accountdatemonthyeartitlequantityvalueMPUactiveformatsizesvolumeunit pricezipcodecitystate
amazonSunday, 14 April, 2024April2024Equal® Classic Jar (100g) | Zero Calorie Sweetener | No Aspartame | Diet Friendly |Table Top Sweetener | Powder Formulation | Value Pack | Pack of 123001SucraloseJar100 gm2150700074KOLKATAWest Bengal
amazonWednesday, 3 April, 2024April2024Equal Stevia Natural Sweetener, Sugar Free, 50 Sachet, Pack of 123001SteviaSachets502150741159BIRNAGARWest Bengal
amazonSunday, 31 March, 2024March2024Equal Stevia Natural Sweetener, Sugar Free, 50 Sachet, Pack of 123001SteviaSachets502150110096NEW DELHIDelhi
amazonMonday, 20 May, 2024May2024Equal® Stevia Plant-Based 100% Natural Sweetener | Sugar Free | Diabetic Friendly | Vegan & Keto Friendly | 300 Tablets | Pack of 1 (Pack of 3)218001SteviaTablets3002900695010THIRUVANANTHAPURAMKerala
amazonWednesday, 1 November, 2023November2023Equal® Stevia 500 Tablets | Plant-Based Natural Sweetener | 100% Natural Sweetness from Stevia | Zero Calorie from Stevia | Tastes Like Sugar | Ideal26101SteviaTablets5002305768001SAMBALPUROdisha

 

What I am trying to achieve is as follows :

Depending on the combination of the values chosen on the slicers, it should return a table of top 10 cities basis purchase frequency :

monojchakrab_0-1716356905540.png

The measure I am working with is as below :

 

 

Top 10 Cities = 
VAR _selectedFormat = SELECTEDVALUE('ecomm consolidated'[format])
VAR _selectedActive = SELECTEDVALUE('ecomm consolidated'[active])
VAR _selectedPack = SELECTEDVALUE('ecomm consolidated'[sizes])

VAR FilteredCities = 
    FILTER(
        VALUES('ecomm consolidated'[city]),
        'ecomm consolidated'[city] <> "xxxx"
    )
VAR PurchaseFrequencyTable =
    ADDCOLUMNS(
        FilteredCities,
        "Purchase Frequency", 
        CALCULATE(
            COUNTROWS('ecomm consolidated'),
            'ecomm consolidated'[Format] = _selectedFormat,
            'ecomm consolidated'[Active] = _selectedActive,
            'ecomm consolidated'[Sizes] = _selectedPack
        )
    )
// VAR RankedTable =
//     ADDCOLUMNS(
//         PurchaseFrequencyTable,
//         "Rank", 
//         RANKX(
//             PurchaseFrequencyTable,
//             [Purchase Frequency],
//             ,
//             DESC,
//             DENSE
//         )
//     )
VAR Top10Cities =
    TOPN(
        10,
        PurchaseFrequencyTable,
        [Purchase Frequency], DESC
    )
RETURN
    SELECTCOLUMNS(
        Top10Cities,
        "City", [city],
        "Purchase Frequency", [Purchase Frequency]
    )
    

 

 

But as shown above in the screenshot, it returns a blank table - not sure what am I missing. I tested the selectedvalue measures separately and those work. But the purchasefrequencytable does not return. The list of cities does not filter to T10 and if I add the purchase frequency, the table turns blank.

 

Any help much appreciated

 

Best regds.,

 

 

 

 

 

 

 

3 REPLIES 3
monojchakrab
Resolver III
Resolver III

I am continuing on this thread as my existing question still remains unresolved. While trying to debug the code, I found the following :

1. When I am forcing the filter to choose the parameters manually as below : 

Top cities = 
// VAR _selectedFormat = SELECTEDVALUE('ecomm consolidated'[format])
// VAR _selectedActive = SELECTEDVALUE('ecomm consolidated'[active])
// VAR _selectedPack = SELECTEDVALUE('ecomm consolidated'[sizes])

VAR Table_purchase_frequency =

SUMMARIZE(
    CALCULATETABLE(
        'ecomm consolidated',
        FILTER(
            'ecomm consolidated',
            'ecomm consolidated'[city] <> "xxxx" &&
            'ecomm consolidated'[active] = "aspartame" &&  -- Replace with slicer selection if needed
            'ecomm consolidated'[format] = "tablets" &&  -- Replace with slicer selection if needed
            'ecomm consolidated'[sizes] = "100"  -- Replace with slicer selection if needed
        )
    ),
    'ecomm consolidated'[city],
    "Purchase Frequency", COUNTROWS('ecomm consolidated')
)

RETURN

TOPN(10,

Table_purchase_frequency,

[Purchase Frequency],

DESC)

It seems to return the correct table, even though its not sorted in descending order as it should have.

monojchakrab_0-1716618211327.png

 

2. But when I am trying to feed the filter function from the values chosen dynamically from the slicers on the page, it returns a blank table.

Top cities = 
VAR _selectedFormat = SELECTEDVALUE('ecomm consolidated'[format])
VAR _selectedActive = SELECTEDVALUE('ecomm consolidated'[active])
VAR _selectedPack = SELECTEDVALUE('ecomm consolidated'[sizes])

VAR Table_purchase_frequency =

SUMMARIZE(
    CALCULATETABLE(
        'ecomm consolidated',
        FILTER(
            'ecomm consolidated',
            'ecomm consolidated'[city] <> "xxxx" &&
            'ecomm consolidated'[active] = _selectedActive &&  -- Replace with slicer selection if needed
            'ecomm consolidated'[format] = _selectedFormat &&  -- Replace with slicer selection if needed
            'ecomm consolidated'[sizes] = _selectedPack -- Replace with slicer selection if needed
        )
    ),
    'ecomm consolidated'[city],
    "Purchase Frequency", COUNTROWS('ecomm consolidated')
)

RETURN

TOPN(10,

Table_purchase_frequency,

[Purchase Frequency],

DESC)

 monojchakrab_1-1716618377209.png

3. But when I checked the slicers individually,

monojchakrab_2-1716618824171.png

here's the code : 

Slicer selection = 
    VAR _selectedFormat = SELECTEDVALUE('ecomm consolidated'[format])
    VAR _selectedActive = SELECTEDVALUE('ecomm consolidated'[active])
    VAR _selectedPack = SELECTEDVALUE('ecomm consolidated'[sizes])

RETURN

    _selectedActive & "-" & _selectedFormat &"-" & _selectedPack

Is there something I am missing? any help appreciated.

appreciate and best regds.,

v-xuxinyi-msft
Community Support
Community Support

Hi @monojchakrab 

 

I am a little confused about your request. What is the logic for calculating the frequency of purchases? Is it counting the number of rows based on the selections in the slicer? And then display the top 10 purchase frequency?

 

Best Regards,
Yulia Xu

@v-xuxinyi-msft - I want to return the T10 cities, based on the purchase frequency of the combination of the values on the slicers...e.g. which are the T10 cities for Stevia 100 tablets, basis the purchase freuqncy of this combination chosen from the slicers. Similalry, if I change the slicer figures to stevia 100 sachets, it should return the T10 cities for than combination. If I get this code right, I can then toggle from purchase fequency to volume or value too. 

Does that answer your question?

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.