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,
I have a report that has daily data which can be filtered by date. I also need to show the dates in the title. I have that much sorted out using https://blog.crossjoin.co.uk/2016/04/25/dynamic-chart-titles-in-power-bi/
I have a measure of
ListOfDays = Concatenatex(Values(DD[Day Date]), DD[Day Date], ", ", DD[Day Date], ASC)
But it is outputting data like
12/23/2017, 12/26/2017, 12/27/2017, 12/28/2017, 12/29/2017, 12/30/2017, ...
How would I do it such that the output would be:
12/23/2017, 12/26-30/2017, 1/3/2018, 1/5-7/2018, 1/9, 1/11
Basic rules, only show the year on the last half of a span. Only show the year for a single date if it changes from the previous shown date.
From what I can tell, Looping and building a string are not options in Power BI.
Thanks,
IMarv
Solved! Go to Solution.
Hi @IMarvinTPA
DAX can compress vertical sequences, but it's not super optimised for it.
This is pretty close, but you can see it's not a trivial peice of code.
But this measure.
Date Measure = VAR A = VALUES('DD'[Day Date]) VAR B = CALENDAR(MIN('DD'[Day Date]),MAX('DD'[Day Date])) VAR Gaps = SELECTCOLUMNS(EXCEPT(B,A),"Gap Day",[Date]) VAR C = SELECTCOLUMNS(A,"End Date",[Day Date]) --- Get a bunch of possible sequences VAR D = GENERATE( A, FILTER( C, [End Date] >= [Day Date]) ) -- Make a list of sequences WTIH a gap in them VAR E = SELECTCOLUMNS( GENERATE( D , FILTER( Gaps, [Gap Day]>=[Day Date] && [Gap Day] <= [End Date]) ), "Day Date",[Day Date] , "End Date",[End Date]) VAR F = EXCEPT(D,E) VAR G = SELECTCOLUMNS( GENERATE( F, FILTER( SELECTCOLUMNS( f, "D",[Day Date], "E",[End Date] ) , ([D]>[Day Date] && [D]<=[End Date]) || ([E]>=[Day Date] && [E]<[End Date]) ) ) ,"D",[D],"E",[E]) VAR H = SELECTCOLUMNS( EXCEPT(F,G), "Output", IF( [Day Date] = [End Date], FORMAT([Day Date],"DD/MM/YYYY"), FORMAT([Day Date],"DD/MM") & "-" & FORMAT([End Date],"DD/MM/YYYY") )) RETURN CONCATENATEX(H,[Output], ", ")
Compresses this data
Into this
Phew!
Hi, i recently read this interesting question.
My alternative to solve it will be:
Selected = VAR SELECTIONS = VALUES ( Valores[Value] ) VAR SERIEWITHEEXTRACOLUMNS = ADDCOLUMNS ( SELECTIONS, "M-1", Valores[Value] - 1, "M+1", Valores[Value] + 1 ) VAR MINVALUESELECTED = MIN ( Valores[Value] ) RETURN IF ( HASONEVALUE ( Valores[Value] ), FORMAT ( SELECTEDVALUE ( Valores[Value] ), "DD/MM/YYYY" ), IF ( ISFILTERED ( Valores[Value] ), CONCATENATEX ( SERIEWITHEEXTRACOLUMNS, IF ( COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M-1] ) ) > 0 && COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M+1] ) ) > 0, BLANK (), IF ( COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M+1] ) ) < 1 && COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M-1] ) ) > 0, "-" & FORMAT ( Valores[Value], "DD/MM/YYYY" ), IF ( Valores[Value] = MINVALUESELECTED, FORMAT ( Valores[Value], "DD/MM" ), IF ( COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M+1] ) ) < 1 && COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M-1] ) ) < 1, ", " & FORMAT ( Valores[Value], "DD/MM/YYYY" ), ", " & FORMAT ( Valores[Value], "DD/MM" ) ) ) ) ) ), "NO SELECTED" ) )
Regards
Victor
Hi @IMarvinTPA
DAX can compress vertical sequences, but it's not super optimised for it.
This is pretty close, but you can see it's not a trivial peice of code.
But this measure.
Date Measure = VAR A = VALUES('DD'[Day Date]) VAR B = CALENDAR(MIN('DD'[Day Date]),MAX('DD'[Day Date])) VAR Gaps = SELECTCOLUMNS(EXCEPT(B,A),"Gap Day",[Date]) VAR C = SELECTCOLUMNS(A,"End Date",[Day Date]) --- Get a bunch of possible sequences VAR D = GENERATE( A, FILTER( C, [End Date] >= [Day Date]) ) -- Make a list of sequences WTIH a gap in them VAR E = SELECTCOLUMNS( GENERATE( D , FILTER( Gaps, [Gap Day]>=[Day Date] && [Gap Day] <= [End Date]) ), "Day Date",[Day Date] , "End Date",[End Date]) VAR F = EXCEPT(D,E) VAR G = SELECTCOLUMNS( GENERATE( F, FILTER( SELECTCOLUMNS( f, "D",[Day Date], "E",[End Date] ) , ([D]>[Day Date] && [D]<=[End Date]) || ([E]>=[Day Date] && [E]<[End Date]) ) ) ,"D",[D],"E",[E]) VAR H = SELECTCOLUMNS( EXCEPT(F,G), "Output", IF( [Day Date] = [End Date], FORMAT([Day Date],"DD/MM/YYYY"), FORMAT([Day Date],"DD/MM") & "-" & FORMAT([End Date],"DD/MM/YYYY") )) RETURN CONCATENATEX(H,[Output], ", ")
Compresses this data
Into this
Phew!
Wow.
I knew it wasn't going to be easy. I'll be studying this for a while. The mindset it takes to operate in this direction is very different from a procedural one.
Thank you!
IMarv
Tried it with more data and it results in a farily interesting expansion of values. I'll see if I can tweak it to actually work.
April 1-28th looks like (No data on the 8th, and 15):
04/01-04/02/2018, 04/01-04/03/2018, 04/02-04/03/2018, 04/01-04/04/2018, 04/02-04/04/2018, 04/03-04/04/2018, 04/01-04/05/2018, 04/02-04/05/2018, 04/03-04/05/2018, 04/04-04/05/2018, 04/01-04/07/2018, 04/02-04/07/2018, 04/03-04/07/2018, 04/04-04/07/2018, 04/05-04/07/2018, 04/06-04/07/2018, 04/01-04/06/2018, 04/02-04/06/2018, 04/03-04/06/2018, 04/04-04/06/2018, 04/05-04/06/2018, 04/01-04/09/2018, 04/02-04/09/2018, 04/03-04/09/2018, 04/04-04/09/2018, 04/05-04/09/2018, 04/07-04/09/2018, 04/06-04/09/2018, 04/01-04/10/2018, 04/02-04/10/2018, 04/03-04/10/2018, 04/04-04/10/2018, 04/05-04/10/2018, 04/07-04/10/2018, 04/06-04/10/2018, 04/09-04/10/2018, 04/01-04/11/2018, 04/02-04/11/2018, 04/03-04/11/2018, 04/04-04/11/2018, 04/05-04/11/2018, 04/07-04/11/2018, 04/06-04/11/2018, 04/09-04/11/2018, 04/10-04/11/2018, 04/01-04/12/2018, 04/02-04/12/2018, 04/03-04/12/2018, 04/04-04/12/2018, 04/05-04/12/2018, 04/07-04/12/2018, 04/06-04/12/2018, 04/09-04/12/2018, 04/10-04/12/2018, 04/11-04/12/2018, 04/01-04/13/2018, 04/02-04/13/2018, 04/03-04/13/2018, 04/04-04/13/2018, 04/05-04/13/2018, 04/07-04/13/2018, 04/06-04/13/2018, 04/09-04/13/2018, 04/10-04/13/2018, 04/11-04/13/2018, 04/12-04/13/2018, 04/01-04/14/2018, 04/02-04/14/2018, 04/03-04/14/2018, 04/04-04/14/2018, 04/05-04/14/2018, 04/07-04/14/2018, 04/06-04/14/2018, 04/09-04/14/2018, 04/10-04/14/2018, 04/11-04/14/2018, 04/12-04/14/2018, 04/13-04/14/2018, 04/01-04/16/2018, 04/02-04/16/2018, 04/03-04/16/2018, 04/04-04/16/2018, 04/05-04/16/2018, 04/07-04/16/2018, 04/06-04/16/2018, 04/09-04/16/2018, 04/10-04/16/2018, 04/11-04/16/2018, 04/12-04/16/2018, 04/13-04/16/2018, 04/14-04/16/2018, 04/01-04/17/2018, 04/02-04/17/2018, 04/03-04/17/2018, 04/04-04/17/2018, 04/05-04/17/2018, 04/07-04/17/2018, 04/06-04/17/2018, 04/09-04/17/2018, 04/10-04/17/2018, 04/11-04/17/2018, 04/12-04/17/2018, 04/13-04/17/2018, 04/14-04/17/2018, 04/16-04/17/2018, 04/01-04/18/2018, 04/02-04/18/2018, 04/03-04/18/2018, 04/04-04/18/2018, 04/05-04/18/2018, 04/07-04/18/2018, 04/06-04/18/2018, 04/09-04/18/2018, 04/10-04/18/2018, 04/11-04/18/2018, 04/12-04/18/2018, 04/13-04/18/2018, 04/14-04/18/2018, 04/16-04/18/2018, 04/17-04/18/2018, 04/01-04/19/2018, 04/02-04/19/2018, 04/03-04/19/2018, 04/04-04/19/2018, 04/05-04/19/2018, 04/07-04/19/2018, 04/06-04/19/2018, 04/09-04/19/2018, 04/10-04/19/2018, 04/11-04/19/2018, 04/12-04/19/2018, 04/13-04/19/2018, 04/14-04/19/2018, 04/16-04/19/2018, 04/17-04/19/2018, 04/18-04/19/2018, 04/01-04/20/2018, 04/02-04/20/2018, 04/03-04/20/2018, 04/04-04/20/2018, 04/05-04/20/2018, 04/07-04/20/2018, 04/06-04/20/2018, 04/09-04/20/2018, 04/10-04/20/2018, 04/11-04/20/2018, 04/12-04/20/2018, 04/13-04/20/2018, 04/14-04/20/2018, 04/16-04/20/2018, 04/17-04/20/2018, 04/18-04/20/2018, 04/19-04/20/2018, 04/01-04/21/2018, 04/02-04/21/2018, 04/03-04/21/2018, 04/04-04/21/2018, 04/05-04/21/2018, 04/07-04/21/2018, 04/06-04/21/2018, 04/09-04/21/2018, 04/10-04/21/2018, 04/11-04/21/2018, 04/12-04/21/2018, 04/13-04/21/2018, 04/14-04/21/2018, 04/16-04/21/2018, 04/17-04/21/2018, 04/18-04/21/2018, 04/19-04/21/2018, 04/20-04/21/2018, 04/01-04/23/2018, 04/02-04/23/2018, 04/03-04/23/2018, 04/04-04/23/2018, 04/05-04/23/2018, 04/07-04/23/2018, 04/06-04/23/2018, 04/09-04/23/2018, 04/10-04/23/2018, 04/11-04/23/2018, 04/12-04/23/2018, 04/13-04/23/2018, 04/14-04/23/2018, 04/16-04/23/2018, 04/17-04/23/2018, 04/18-04/23/2018, 04/19-04/23/2018, 04/20-04/23/2018, 04/21-04/23/2018, 04/22-04/23/2018, 04/01-04/22/2018, 04/02-04/22/2018, 04/03-04/22/2018, 04/04-04/22/2018, 04/05-04/22/2018, 04/07-04/22/2018, 04/06-04/22/2018, 04/09-04/22/2018, 04/10-04/22/2018, 04/11-04/22/2018, 04/12-04/22/2018, 04/13-04/22/2018, 04/14-04/22/2018, 04/16-04/22/2018, 04/17-04/22/2018, 04/18-04/22/2018, 04/19-04/22/2018, 04/20-04/22/2018, 04/21-04/22/2018, 04/01-04/24/2018, 04/02-04/24/2018, 04/03-04/24/2018, 04/04-04/24/2018, 04/05-04/24/2018, 04/07-04/24/2018, 04/06-04/24/2018, 04/09-04/24/2018, 04/10-04/24/2018, 04/11-04/24/2018, 04/12-04/24/2018, 04/13-04/24/2018, 04/14-04/24/2018, 04/16-04/24/2018, 04/17-04/24/2018, 04/18-04/24/2018, 04/19-04/24/2018, 04/20-04/24/2018, 04/21-04/24/2018, 04/23-04/24/2018, 04/22-04/24/2018, 04/01-04/25/2018, 04/02-04/25/2018, 04/03-04/25/2018, 04/04-04/25/2018, 04/05-04/25/2018, 04/07-04/25/2018, 04/06-04/25/2018, 04/09-04/25/2018, 04/10-04/25/2018, 04/11-04/25/2018, 04/12-04/25/2018, 04/13-04/25/2018, 04/14-04/25/2018, 04/16-04/25/2018, 04/17-04/25/2018, 04/18-04/25/2018, 04/19-04/25/2018, 04/20-04/25/2018, 04/21-04/25/2018, 04/23-04/25/2018, 04/22-04/25/2018, 04/24-04/25/2018, 04/01-04/26/2018, 04/02-04/26/2018, 04/03-04/26/2018, 04/04-04/26/2018, 04/05-04/26/2018, 04/07-04/26/2018, 04/06-04/26/2018, 04/09-04/26/2018, 04/10-04/26/2018, 04/11-04/26/2018, 04/12-04/26/2018, 04/13-04/26/2018, 04/14-04/26/2018, 04/16-04/26/2018, 04/17-04/26/2018, 04/18-04/26/2018, 04/19-04/26/2018, 04/20-04/26/2018, 04/21-04/26/2018, 04/23-04/26/2018, 04/22-04/26/2018, 04/24-04/26/2018, 04/25-04/26/2018, 04/01-04/27/2018, 04/02-04/27/2018, 04/03-04/27/2018, 04/04-04/27/2018, 04/05-04/27/2018, 04/07-04/27/2018, 04/06-04/27/2018, 04/09-04/27/2018, 04/10-04/27/2018, 04/11-04/27/2018, 04/12-04/27/2018, 04/13-04/27/2018, 04/14-04/27/2018, 04/16-04/27/2018, 04/17-04/27/2018, 04/18-04/27/2018, 04/19-04/27/2018, 04/20-04/27/2018, 04/21-04/27/2018, 04/23-04/27/2018, 04/22-04/27/2018, 04/24-04/27/2018, 04/25-04/27/2018, 04/26-04/27/2018, 04/01-04/28/2018, 04/02-04/28/2018, 04/03-04/28/2018, 04/04-04/28/2018, 04/05-04/28/2018, 04/07-04/28/2018, 04/06-04/28/2018, 04/09-04/28/2018, 04/10-04/28/2018, 04/11-04/28/2018, 04/12-04/28/2018, 04/13-04/28/2018, 04/14-04/28/2018, 04/16-04/28/2018, 04/17-04/28/2018, 04/18-04/28/2018, 04/19-04/28/2018, 04/20-04/28/2018, 04/21-04/28/2018, 04/23-04/28/2018, 04/22-04/28/2018, 04/24-04/28/2018, 04/25-04/28/2018, 04/26-04/28/2018, 04/27-04/28/2018
Thanks,
IMarv
For some reason the
SELECTCOLUMNS( f, "D",[Day Date], "E",[End Date] )
in
VAR G = SELECTCOLUMNS( GENERATE( F, FILTER( SELECTCOLUMNS( f, "D",[Day Date], "E",[End Date] ) , ([D]>[Day Date] && [D]<=[End Date]) || ([E]>=[Day Date] && [E]<[End Date]) ) ) ,"D",[D],"E",[E])
was behaving as if it were
SELECTCOLUMNS( f, "D",[Day Date], "E",[Day Date] )
Resulting in a bad comparison.
I eventually fixed it by replacing it with
SELECTCOLUMNS(F, "E", Date(Year([End Date]), MONTH([End Date]), Day([End Date])), "D", [Day Date])
Edit: Also had to update E:
-- Make a list of sequences WTIH a gap in them VAR E = SELECTCOLUMNS( GENERATE( D , FILTER( Gaps, [Gap Day]>=[Day Date] && [Gap Day] <= [End Date]) ), "Day Date", [Day Date] , "End Date", Date(Year([End Date]), MONTH([End Date]), Day([End Date])))
Which forced it to do it correctly.
I'm not sure what is wrong with it internally, but this seems to be a case of me fighting an environment bug.
Thanks,
IMarv
I think I know what the problem is and I can test a change locally.
I think the SELECTCOLUMNS function is getting the order randomly around the wrong way. I think if I nest using ADDCOLUMNS it should make it more robust (the column ordering)
Hi @IMarvinTPA
EUREKA!!!
I found and fixed the problem. The issue is to do with lineage and I suspect there are issues in the engine, but the fix is just to add a +0 to two places in the code. I have highlighed the two places in red, but basically at the end of the 2nd columns in both SELECTCOLUMN calls.
Date Measure = VAR A = VALUES('DD'[Day Date]) VAR B = CALENDAR(MIN('DD'[Day Date]),MAX('DD'[Day Date])) VAR Gaps = SELECTCOLUMNS(EXCEPT(B,A),"Gap Day",[Date]) VAR C = SELECTCOLUMNS(A,"End Date",[Day Date]) --- Get a bunch of possible sequences VAR D = GENERATE( A, FILTER( C, [End Date] >= [Day Date]) ) -- Make a list of sequences WTIH a gap in them VAR E = SELECTCOLUMNS( GENERATE( D , FILTER( Gaps, [Gap Day]>=[Day Date] && [Gap Day] <= [End Date]) ), "Day Date",[Day Date] , "End Date",[End Date] +0 ) VAR F = EXCEPT(D,E) VAR G = SELECTCOLUMNS( GENERATE( F, FILTER( SELECTCOLUMNS( f, "D",[Day Date] , "E",[End Date] +0 ) , ([D]>[Day Date] && [D]<=[End Date]) || ([E]>=[Day Date] && [E]<[End Date]) ) ) ,"D",[D],"E",[E]) VAR H = SELECTCOLUMNS( EXCEPT(F,G), "Output", IF( [Day Date] = [End Date], FORMAT([Day Date],"DD/MM/YYYY"), FORMAT([Day Date],"DD/MM") & "-" & FORMAT([End Date],"DD/MM/YYYY") )) RETURN CONCATENATEX(H,[Output], ", ")
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 |
---|---|
96 | |
95 | |
80 | |
74 | |
66 |
User | Count |
---|---|
130 | |
106 | |
105 | |
86 | |
72 |