topic Re: please Help Dax for smart narrative in DAX Commands and Tips

please Help Dax for smart narrative

firstch — Tue, 21 Jun 2022 10:32:27 GMT

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

Re: please Help Dax for smart narrative

v-yalanwu-msft — Fri, 24 Jun 2022 07:18:12 GMT

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.

Re: please Help Dax for smart narrative

firstch — Mon, 04 Jul 2022 02:57:06 GMT

hi v-yalanwu-msft

Thankyou for your help but it still error sir

Re: please Help Dax for smart narrative

v-yalanwu-msft — Mon, 04 Jul 2022 03:16:25 GMT

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

Re: please Help Dax for smart narrative

firstch — Mon, 04 Jul 2022 04:31:46 GMT

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

Re: please Help Dax for smart narrative

v-yalanwu-msft — Mon, 04 Jul 2022 09:55:40 GMT

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.

Re: please Help Dax for smart narrative

firstch — Mon, 04 Jul 2022 10:18:45 GMT

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

Re: please Help Dax for smart narrative

v-yalanwu-msft — Tue, 05 Jul 2022 07:26:45 GMT

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.

Re: please Help Dax for smart narrative

firstch — Tue, 05 Jul 2022 21:16:07 GMT

This one is working thankyou so much sir

Best Regard
firstch