Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi team,
I have a date field from which i am extracting month :
Month Name = SWITCH(FSP[Month],1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")
Now i have created a measure "title month " (formula below) and based on the slicer selection it shows the month selected.
Title Month = " Month: " & CONCATENATEX( VALUES( FSP[Month Name]), FSP[Month Name], ", ")
This is working fine. but its not sorting as per the month. it gives wierd header (not sorted according to selection slicer; but random) . I want it sorted
e.g. if march, june, january is selected Show as "Month: January, march June
and if selected all Show as "Month: All"
P.s: in the dataset month name is sorted based on month
Please help me. If have attached screenshots
Hi all!
It seems we don't have a solution yet for this issue using CONCATENATEX... I am facing the same problem rightnow I don't know how to fix it! Had anyone discovered how to do so? Please share 🙂
Take a look at the picture I am attaching.
Hi @bajimmy1983
use the following steps :
Let me know if this works
Thanks
Ronak
Hi @Anonymous ! First thanks a lot to reply.
Please see in this new photo I already have "Sort by Column" configured. All my charts are ok, too (Jan, Feb, Mar, Apr and so on), but just using CONCATENATEX function this "sort by" lost itself.
Measure I'm using:
I colored by blue in the "Month" part.
I want to use the same approach (TOPN function) Months as I used for Years, but when I apply TOPN function like below I got that first error I shared in the first photo (months are not sorted correctly).
TOPN ( __MAX_VALUES_TO_SHOW_MONTH; VALUES ( 'Date'[MONTH] ); 'Date'[MONTH]; ASC ); 'Date'[MONTH]; ", "; 'Date'[MONTH]; ASC )
List of Year-Months values = VAR __DISTINCT_VALUES_COUNT_YEAR = DISTINCTCOUNT ( 'Date'[Year] ) VAR __DISTINCT_VALUES_COUNT_MONTH = DISTINCTCOUNT ( 'Date'[MONTH] ) VAR __MAX_VALUES_TO_SHOW_YEAR = 6 VAR __MAX_VALUES_TO_SHOW_MONTH = 6 RETURN CONCATENATE ( IF ( ISFILTERED ( 'Date'[Year] ); IF ( __DISTINCT_VALUES_COUNT_YEAR > __MAX_VALUES_TO_SHOW_YEAR; CONCATENATE ( "Dados de: "; CONCATENATE ( CONCATENATEX ( TOPN ( __MAX_VALUES_TO_SHOW_YEAR; VALUES ( 'Date'[Year] ); 'Date'[Year]; ASC ); 'Date'[Year]; ", "; 'Date'[Year]; ASC ); ", etc. " ) ); CONCATENATE ( "Dados de: "; CONCATENATEX ( VALUES ( 'Date'[Year] ); 'Date'[Year]; ", "; 'Date'[Year]; ASC ) ) ); "Dados de todos os Anos " ); IF ( ISFILTERED ( 'Date'[MONTH] ); IF ( __DISTINCT_VALUES_COUNT_MONTH > __MAX_VALUES_TO_SHOW_MONTH; " e vários Meses"; CONCATENATE ( " e Meses: "; CONCATENATEX ( VALUES ( 'Date'[MONTH] ); 'Date'[MONTH]; ", "; 'Date'[MONTH]; ASC ) ) ); " e todos Meses" ) )
Photo:
I created Dates table
So, the order returned is the order encountered within the data. What I would suggest is that you sort your data on import in Power Query based upon the Month column. Then, you order will be correct in your DAX.
No this is not working. Also because I am not importing ; I am using a direct query with joins
Hi ronakvi14,
CONCATENATEX can set sorting option like pattern below:
CONCATENATEX ( <table>, <expression>, [<delimiter>], [<OrderBy_Expression1>], [<Order>] … )
Please refer to: https://www.tutorialspoint.com/dax_functions/dax_concatenatex_function.htm .
Regards,
Jimmy Tao
Hi ,
I tried using this expression
Title Month = " Month: " & CONCATENATEX( FSP, FSP[Month Name], ", ", FSP[Month],asc)
but its giving the error
Error Message:
MdxScript(Model) (9, 27) Calculation error in measure 'FSP'[Title Month]: Function 'PLACEHOLDER' encountered a Text that exceeds the maximum allowable length.
fsp[month] has numeric data (1,2,3 correcsponding to the month)
fsp[month name] has jan,feb, ,march
need to sort by month in dynamic header rather than the month name.. but display the months selected by the user (its a slicer)
I think that you still need to use VALUES to get your unique list e.g.
Title = "Months: " & IF(COUNTROWS(VALUES(Months)) = 12, "All" ,CONCATENATEX(VALUES(Months), Months[Name], ", ", Months[Num]))
If i write this :
Title = "Months: " & IF(COUNTROWS(VALUES(FSP[Month])) = 12, "All" ,CONCATENATEX(VALUES(FSP[Month]), FSP[Month], ", ", FSP[Month]))
this is just giving me month numbers sorted..
but i want month name sorted by month numbers
If I try this:
Title m = "Months: " & IF(COUNTROWS(VALUES(FSP[Month])) = 12, "All" ,CONCATENATEX(VALUES(FSP[Month Name]), FSP[Month Name], ", ", FSP[Month]))
it gives me the error: a table of multiple values was supplied where a single value was expected
Try MIN for the order and I think that DISTINCT is better then VALUES
Title m = "Months: " & IF(COUNTROWS(DISTINCT(FSP[Month])) = 12, "All" ,CONCATENATEX(DISTINCT(FSP[Month Name]), FSP[Month Name], ", ", MIN(FSP[Month])))
Its not sorting. maybe thats a limitation.
Not sure. Have been looking into blogs and videos in order to resolve this.
Very strange. Can you isolate dataset and share with me? It is so transparent that it must work.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |