Forum Discussion

firstch's avatar
firstch
Icon for Helper III rankHelper III
4 years ago
Solved

please Help Dax for smart narrative

I really need help for using function concatenatex for the top 3 value for smart narrative 

how to solve  this  error smart narrative, i try to show top 3 country by total value but the country that have that product have only vietnam and china so its error as picture ,but other product workfine

this is my DAX 

 




thankyou in advance for your help

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi， firstch ；

    You could change it such as:

    rank = 
CONCATENATEX (
    FILTER (
        ALLSELECTED ( 'financials'[Country] ),
        RANKX (
            ALL ( financials ),
            CALCULATE ( [total], ALLEXCEPT ( financials, 'financials'[Country] ) ),
            ,
            DESC,
            DENSE
        ) <= 3
    ),
    'financials'[Country],
    ",",
    [total], DESC
)

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, firstch ,

    You could try it.

    rank = 
CONCATENATEX ( FILTER( ALLSELECTED( 'Table'),[product]=MAX([product])&&
        RANKX (
            FILTER( ALLSELECTED ( 'Table'),[product]=MAX([product])),
            CALCULATE ([Total]),
            ,
            DESC,
            DENSE
    )
   
 <= 3
    ),
   'Table'[country],
    ",",
   [Total], DESC
)

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi， firstch ；

    You could change it such as:

    rank = 
CONCATENATEX (
    FILTER (
        ALLSELECTED ( 'financials'[Country] ),
        RANKX (
            ALL ( financials ),
            CALCULATE ( [total], ALLEXCEPT ( financials, 'financials'[Country] ) ),
            ,
            DESC,
            DENSE
        ) <= 3
    ),
    'financials'[Country],
    ",",
    [total], DESC
)

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi， firstch ，

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

     

    • firstch's avatar
      firstch
      Icon for Helper III rankHelper III
      4 years ago

      Hi v-yalanwu-msft 
      this is my sample data 

      yearproductcountrycontinentvalue
      2020carbonBangladeshAsia111111
      2020carbonLaosAsia444444
      2020carbonBangladeshAsia4888888
      2020carbonLaosAsia2400000
      2020steelChinaAsia11111
      2020steelVietnamAsia144444
      2020steelChinaAsia14815254
      2020steelVietnamAsia42000000
      2020copperGreeceEurope187080
      2020copperCambodiaAsia6631295
      2020copperChileSouth America274633
      2020copperSaudi ArabiaAsia60
      2020copperJapanAsia1266473
      2020copperTurkeyAsia337936
      2020copperTunisiaAfrica2370000
      2020copperNorwayEurope4173180
      2020copperNicaraguaNorth America66666
      2020copperNew ZealandOceania77777
      2020copperBrazilSouth America2469459393
      2020copperBangladeshAsia2
      2020goldBulgariaEurope547704
      2020goldPakistanAsia1606870
      2020goldParaguaySouth America50955312
      2020goldFranceEurope8934410
      2020goldFinlandEurope5497000
      2020goldPhilippinesAsia156250

      as data from table i want to concatenatex top 3 country by value but some product have 2 country so it error as below 

      Best Regard 
      firstch

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi， firstch ，

    I test your data, the result as below:

    rank = 
CONCATENATEX (
    FILTER (
        ALLSELECTED ( 'Table'[country] ),
        RANKX (
            ALL ( 'Table'),
            CALCULATE ([Total], ALLEXCEPT ( 'Table','Table'[country]) ),
            ,
            DESC,
            DENSE
        ) <= 3
    ),
    'Table'[country],
    ",",
   [Total], DESC
)

    The final show:

    Or what the result you want to output?
    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • firstch's avatar
      firstch
      Icon for Helper III rankHelper III
      4 years ago

      Hi v-yalanwu-msft 
      could you please include slicer with  product carbon ,output it might concat all country as picture 
      the real output that i want is concat top 3 country name althrough it have only 2 country that have that product
      best regard
      firstch 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, firstch ,

    You could try it.

    rank = 
CONCATENATEX ( FILTER( ALLSELECTED( 'Table'),[product]=MAX([product])&&
        RANKX (
            FILTER( ALLSELECTED ( 'Table'),[product]=MAX([product])),
            CALCULATE ([Total]),
            ,
            DESC,
            DENSE
    )
   
 <= 3
    ),
   'Table'[country],
    ",",
   [Total], DESC
)

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • firstch's avatar
      firstch
      Icon for Helper III rankHelper III
      4 years ago

      This one is working thankyou so much sir

      Best Regard
      firstch