please Help Dax for smart narrative

[firstch]

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 · Accepted solution]

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 · Accepted solution]

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]

This one is working thankyou so much sir

Best Regard
firstch

[v-yalanwu-msft]

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]

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]

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]

Hi @v-yalanwu-msft 
this is my sample data 

year	product	country	continent	value
2020	carbon	Bangladesh	Asia	111111
2020	carbon	Laos	Asia	444444
2020	carbon	Bangladesh	Asia	4888888
2020	carbon	Laos	Asia	2400000
2020	steel	China	Asia	11111
2020	steel	Vietnam	Asia	144444
2020	steel	China	Asia	14815254
2020	steel	Vietnam	Asia	42000000
2020	copper	Greece	Europe	187080
2020	copper	Cambodia	Asia	6631295
2020	copper	Chile	South America	274633
2020	copper	Saudi Arabia	Asia	60
2020	copper	Japan	Asia	1266473
2020	copper	Turkey	Asia	337936
2020	copper	Tunisia	Africa	2370000
2020	copper	Norway	Europe	4173180
2020	copper	Nicaragua	North America	66666
2020	copper	New Zealand	Oceania	77777
2020	copper	Brazil	South America	2469459393
2020	copper	Bangladesh	Asia	2
2020	gold	Bulgaria	Europe	547704
2020	gold	Pakistan	Asia	1606870
2020	gold	Paraguay	South America	50955312
2020	gold	France	Europe	8934410
2020	gold	Finland	Europe	5497000
2020	gold	Philippines	Asia	156250

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 · Accepted solution]

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.

[firstch]

hi @v-yalanwu-msft 

Thankyou for your help but it still error sir