Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
Hope someone can help!
I want a slicer with the years 2000 until know. When you click on one year you will receive the total count of members that year and other visuals about those members (ex. membership type, gender,country ....).
The difficulty is that members can have different membership durations, of 1 or 2 years. This means that one year in the slicer refers to members who have one of the three membership durations. Like in the ‘durations’ table below.
Year slicer | membership duration 1 | membership duration 2 | membership duration 3 |
2000 | 1999-2000 | 2000-2000 | 2000-2001 |
2001 | 2000-2001 | 2001-2001 | 2001-2002 |
2002 | 2001-2002 | 2002-2002 | 2002-2003 |
2003 | 2002-2003 | 2003-2003 | 2003-2004 |
2004 | 2003-2004 | 2004-2004 | 2004-2005 |
2005 | 2004-2005 | 2005-2005 | 2005-2006 |
2006 | 2005-2006 | 2006-2006 | 2006-2007 |
2007 | 2006-2007 | 2007-2007 | 2007-2008 |
2008 | 2007-2008 | 2008-2008 | 2008-2009 |
2009 | 2008-2009 | 2009-2009 | 2009-2010 |
2010 | 2009-2010 | 2010-2010 | 2010-2011 |
2011 | 2010-2011 | 2011-2011 | 2011-2012 |
2012 | 2011-2012 | 2012-2012 | 2012-2013 |
2013 | 2012-2013 | 2013-2013 | 2013-2014 |
2014 | 2013-2014 | 2014-2014 | 2014-2015 |
2015 | 2014-2015 | 2015-2015 | 2015-2016 |
2016 | 2015-2016 | 2016-2016 | 2016-2017 |
2017 | 2016-2017 | 2017-2017 | 2017-2018 |
2018 | 2017-2018 | 2018-2018 | 2018-2019 |
2019 | 2018-2019 | 2019-2019 | 2019-2020 |
2020 | 2019-2020 | 2020-2020 | 2020-2021 |
In the 'Membership' database I have the following columns (I included some example rows):
ContactId | StartDate membership (Date type) | EndDate membership (Date type) | Membership created (Date type) | Other columns with membership info e.g. Type | Other columns with contact info E.g. country |
45689 | 1 January 2017 | 31 December 2018 | 1 January 2017 | Trainee | Germany |
47546 | 15 February 2018 | 31 December 2018 | 28 April 2017 | Active | France |
41598 | 23 March 2018 | 31 December 2019 | 16 December 2017 | Trainee | Belgium |
58964 | 15 June 2016 | 31 December 2017 | 2 February 2016 | Retired | Hungary |
68465 | 15 June 2014 | 31 December 2014 | 13 April 2013 | Retired | Bulgaria |
65465 | 1 January 2015 | 31 December 2016 | 14 November 2014 | Active | Brazil |
65465 | 1 January 2019 | 31 December 2019 | 24 August 2018 | Active | Congo |
65469 | 1 January 2018 | 31 December 2019 | 1 January 2018 | Active | Netherlands |
I also created a 'Custom Date' table starting from year 2000 until now. So I can create time intelligence measures. The relationship between 'Membership' and 'Custom date' tables is between column 'Membership created' from Membership table and 'custom dates' from Custom date table with a many to one, single relationship.
Now with all this I have no idea how I can make PowerBi understand that e.g. year 2018 in the slicer equals all contacts with a
The problem is that I don’t have a unique column. I would like to link the ‘durations’ table with the ‘membership’ table but this is not possible as I don’t have a matching column.
Maybe M language or DAX code can help me with this. But I have not enough knowledge to figure this out.
Does someone out there have an idea? 😮
Please request more information if not enough was provided.
Thanks already!
Solved! Go to Solution.
Hello @Temminck
You could try to add a disconnect slicer meaning
Query for your Data called "Table"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJRb4MgEADgv0J8bpqpgPioXbqlyfqw7K3xgdqLJVFcbtKk+zX7LftlA+zMmPWNHHcf3MHhEFHGRR6topjspDYSryR5iDMbSGPyCDV0R0AXErOc7y8bekOpNIBdPQF2Ul+jamXRjFHuChjZwhF/S8R9NhFrUrwj8ccW9aAuztui1DWMXMxyn5iSF4n1ecHybfAglAVXLKFtlOm8yUTO6XjFndHgkvmcdPVJ0INLeoVBIZzs6tnoxm54kQvKWSjSuejPTG8NpwFWmtZiSo4au2l/Z87mnh8zXZN9fyEenwZYovxU7TKW359gYrHCNMRPeMI2vW76yZp9mKXX+J8zeXsYzoCt1KePqKp+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ContactId = _t, #"StartDate membership" = _t, #"EndDate membership" = _t, #"Membership created" = _t, #"Other columns with membership info " = _t, #"Other columns with contact info" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ContactId", Int64.Type}, {"StartDate membership", type date}, {"EndDate membership", type date}, {"Membership created", type date}, {"Other columns with membership info ", type text}, {"Other columns with contact info", type text}})
in
#"Changed Type"
create query for the years called "Years"
let
Source = {2000..2020},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}})
in
#"Changed Type"
Create a calculated measure
MemberCount =
VAR Selection =
SELECTEDVALUE ( 'Years'[Year] )
RETURN
CALCULATE(COUNTROWS('Table');filter('Table';AND(YEAR('Table'[StartDate membership])<=Value(Selection);YEAR('Table'[EndDate membership])>=Value(Selection))))
Add Year-Table as filter and a matrix. The result should look like this
with this solution only one year can be selected
Would this fit your needs?
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hi @Temminck
Try this method:
add a measure to the visual level filter of the table visual
Measure =
IF (
(
YEAR ( MAX ( Membership[StartDate membership] ) )
= SELECTEDVALUE ( durations[Year slicer] )
&& (
YEAR ( MAX ( Membership[EndDate membership] ) )
IN {
SELECTEDVALUE ( durations[Year slicer] ),
SELECTEDVALUE ( durations[Year slicer] ) + 1
}
)
)
|| (
YEAR ( MAX ( Membership[EndDate membership] ) )
= SELECTEDVALUE ( durations[Year slicer] )
&& (
YEAR ( MAX ( Membership[StartDate membership] ) )
IN {
SELECTEDVALUE ( durations[Year slicer] ),
SELECTEDVALUE ( durations[Year slicer] ) - 1
}
)
),
1,
0
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-juanli-msft,
Thank you for the measure. However when applying the measure it duplicates my rows and changes dates...
How did you set the relationship? between:
- Calendar table (all dates from 2000 until 2020)
- Duration table (list of years)
- Membership table (start date, end date, create date,...)
Hi @Temminck
Thanks already for the help 👌
I had not the opportunity to completely investigate both solutions as I am working remotely and can not access the network. Waiting for the problem to be fixed… However, I already have the impression both solutions do not completely answer my needs. Will keep you updated as soon as I have access to the data and dashboards again!
Hello @Temminck
were you able to solve the problem with any reply given?
If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
All the best
Jimmy
Hi Jimmy801,
Sorry for the delay, had christmas holidays and sadly no access to the data at home to test answers 😑. Should have waited to post my question. I now know PowerBi has a fast reacting community !
But I am back now. The solutions given are not working, I try to understand why, but get lost in this complicated dashboard 😓
Hi @Jimmy801,
I tried your solution in an empty file like you suggested and managed to make it work after a while. It now also works in the big file !
Huge thanks ❤️
Kind regards, Julie
Hello @Temminck
You could try to add a disconnect slicer meaning
Query for your Data called "Table"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJRb4MgEADgv0J8bpqpgPioXbqlyfqw7K3xgdqLJVFcbtKk+zX7LftlA+zMmPWNHHcf3MHhEFHGRR6topjspDYSryR5iDMbSGPyCDV0R0AXErOc7y8bekOpNIBdPQF2Ul+jamXRjFHuChjZwhF/S8R9NhFrUrwj8ccW9aAuztui1DWMXMxyn5iSF4n1ecHybfAglAVXLKFtlOm8yUTO6XjFndHgkvmcdPVJ0INLeoVBIZzs6tnoxm54kQvKWSjSuejPTG8NpwFWmtZiSo4au2l/Z87mnh8zXZN9fyEenwZYovxU7TKW359gYrHCNMRPeMI2vW76yZp9mKXX+J8zeXsYzoCt1KePqKp+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ContactId = _t, #"StartDate membership" = _t, #"EndDate membership" = _t, #"Membership created" = _t, #"Other columns with membership info " = _t, #"Other columns with contact info" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ContactId", Int64.Type}, {"StartDate membership", type date}, {"EndDate membership", type date}, {"Membership created", type date}, {"Other columns with membership info ", type text}, {"Other columns with contact info", type text}})
in
#"Changed Type"
create query for the years called "Years"
let
Source = {2000..2020},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}})
in
#"Changed Type"
Create a calculated measure
MemberCount =
VAR Selection =
SELECTEDVALUE ( 'Years'[Year] )
RETURN
CALCULATE(COUNTROWS('Table');filter('Table';AND(YEAR('Table'[StartDate membership])<=Value(Selection);YEAR('Table'[EndDate membership])>=Value(Selection))))
Add Year-Table as filter and a matrix. The result should look like this
with this solution only one year can be selected
Would this fit your needs?
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hi @Jimmy801 ,
How can I modify this measure
MemberCount = VAR Selection = SELECTEDVALUE ( 'Years'[Year] ) RETURN CALCULATE(COUNTROWS('Table');filter('Table';AND(YEAR('Table'[StartDate membership])<=Value(Selection);YEAR('Table'[EndDate membership])>=Value(Selection))))
so that if I select a year in the past (in the slicer) I get a table showing member information (id, start date, end date, name, country,...) for the selected year but not those who have a membership this year.
Example:
I select year 2017 in the slicer
-> this selection shows members with a membership duration 2016-2016, 2017-2017 and 2017-2018 in a table
-> members in 2017 who have a membership this year 2020 (2019-2020, 2020-2020, 2020-2021) do not show up in the table
Thanks already for your time!
Btw I soon have a 2day Dax course, will hopefully get better at this 😉
Hello @Temminck
so if i got it right... you selelct 2017 and want to see the memebers that have a memebership in 2017 AND 2020
if this is the case try this
MemberCount =
VAR Selection =
SELECTEDVALUE ( 'Years'[Year] )
RETURN
CALCULATE(COUNTROWS('Table');filter('Table';(YEAR('Table'[StartDate membership])<=Value(Selection)&&YEAR('Table'[EndDate membership])>=Value(Selection))&&(YEAR('Table'[StartDate membership])=2020||YEAR('Table'[EndDate membership])=2020)))
didn't test it, but hopefully works
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @Jimmy801 ,
Think you slightly misunderstood the question. Members in 2017 should NOT show up if they already have a membership this year.
Example:
memberId | startdate membership | enddate membership | country | emailadress |
635464 | 1 jan 2017 | 31 dec 2017 | Germany | julesbram@gmail.com |
635464 | 1 jan 2019 | 31 dec 2020 | Germany | julesbram@gmail.com |
523654 | 1 jan 2016 | 31 dec 2017 | Hungaria | brvaklash@google.com |
If I select year 2017 I only want to see the contact 523654, because he has no current membership. Contact 635464 should not diplay because he has a membership in 2017 but also has a membership this year.
The measure should filter rows to 2017 members AND remove duplicate members (2017 and 2020 members). With keeping in mind that 1 year equals 3 membership durations.
Hello @Temminck
try this measure then
MemberCountWithOutMemberShip2020 =
VAR Selection =
SELECTEDVALUE ( 'Years'[Year] )
RETURN
if ((CALCULATE(COUNTROWS('Table');filter('Table';AND(YEAR('Table'[StartDate membership])<=Value(Selection);YEAR('Table'[EndDate membership])>=Value(Selection))))) >0 && (CALCULATE(COUNTROWS('Table');filter('Table';OR(YEAR('Table'[StartDate membership])=2020;YEAR('Table'[EndDate membership])>=2020))))=0;1;blank())
All the best
Jimmy
In M:
1. Create a table in m with just the ContactId, EndDate, StartDate
2. Add a custom column with:
{Date.Year(DateTime.Date([StartDate])) .. Date.Year(DateTime.Date([EndDate]))}
3. The click on the column and choose "Expand to new rows"
This will generate one row per year as a number.
4. Make a reference to your date table in a new query.
5. In Transform column-> Date -> Year only
6. Remove all other columns
7. Remove duplicates.
8. Make a one to many relationship between these 2 tables.