Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
i have a Column in my dim table containing a header for a table. So I show this over a visual
BUT if the user chooses the year and Month from the date Slicer I want it to show this instead
The example is that the title normally says
Dates from 2020 through to 2024
As soon as the user slices for example 1 year. I want it to say 2023
If a user Slices a year and some months in that year I want it to say 2023 March April
If a user slices multiple things I want it to say 2023 March April 2024 January
Or something along those lines.
Is this possible at all?
Hi @DebbieE ,
For this you need to make a measure that you will use dinamically on your title.
Check this video from Guy in the Cube with Alberto Ferrari
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis is great but it only does months,. It doesnt do months and years and its already really complex. I dont know how to add in years to the mix?
And then the next step for this question is how to show something else. unless the Years / Months are selected?
Hi Debbie,
I did this formula:
Date Information = VAR _maximumdate = MAX('Query1'[Date])
VAR _minimumdate = MIN('Query1'[Date])
VAR _nmonths = COUNTROWS(DISTINCT('Query1'[Year Month]))
VAR _nyears = COUNTROWS(DISTINCT('Query1'[Year]))
VAR _ntotalrows = COUNTROWS('Query1')
VAR _nrowsselected = CALCULATE(
COUNTROWS('Query1'),
DATESBETWEEN(
'Query1'[Date],
_minimumdate,
_maximumdate
),
REMOVEFILTERS('Query1')
)
RETURN
SWITCH(
TRUE(),
_nyears = 1 && _nmonths = 12, FORMAT(
_maximumdate,
"YYYY"
),
_nyears = 1 && _nmonths < 12, MAX('Query1'[Year]) & ": " & CONCATENATEX(
DISTINCT('Query1'[End of Month]),
FORMAT(
'Query1'[End of Month],
"MMMM"
),
", "
),
_nmonths = 1, MIN('Query1'[Year Month]),
_nrowsselected = _ntotalrows, FORMAT(
_minimumdate,
"YYYY MMMM"
) & " to " & FORMAT(
_maximumdate,
"YYYY MMMM"
),
CONCATENATEX(
SUMMARIZE(
'Query1',
'Query1'[Year],
"D", CONCATENATEX(
SUMMARIZE(
'Query1',
'Query1'[End of Month]
),
FORMAT(
'Query1'[End of Month],
"MMMM"
),
", "
)
),
" " & 'Query1'[Year] & ": " & [D],
UNICHAR(10)
)
)
Check the result in the PBIX attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsApologies I have been on Annual leave. Thank you so much for this. However I can already see that this is way to complex for me to achieve in the time i have got.
Im a little confused by End of Month since its not something I have. Would I have to create this in the date dimension as an actual item?
This needs to be a complex formula based on the selected values, you can use the concatanex to make the formula.
I will try to setup an example
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |