Forum Discussion
Really need your help
Hi ham2889,
One sample for your refernce.
1. Create a calculated table as below. Here we needn't create relationship between the new table and the fact table.
Year = VALUES(Table1[Year])
2. Create the measures as below.
Measure = var _count = CALCULATE(DISTINCTCOUNT(Table1[Year]),ALLSELECTED(Table1),VALUES(Table1[IDs])) var _dis = CALCULATE(DISTINCTCOUNT('Year'[Year])) return IF(MAX(Table1[Year]) in VALUES('Year'[Year]),CALCULATE(DISTINCTCOUNT(Table1[IDs]),FILTER(ALLSELECTED(Table1[Year]),_count=_dis)))Measure 2 = CALCULATE(DISTINCTCOUNT(Table1[IDs]),FILTER(Table1,[Measure]<>BLANK()))
For more details, please check the pbix as attached.
Regards,
Frank
Hi ham2889
Below is my solution where I created a very small star schema. This is ideal when creating data models and can easily solve your issue as shown below.
v-frfei-msft solution too works, so you can decide which one works for you.
Here is a link to the PBIX: Really need help
This is an example of my main table. In the real table I have over 10,000 IDs
IDs. Year
101. 2016
102. 2016
103. 2016
102. 2018
101. 2017
103. 2019
102. 2018
102. 2018
102. 2017
103. 2016
102. 2019
101. 2019
101. 2019
This is an example of the table, but I would like to find out how many IDs have worked continually for 4 years.
Looking at this short list it's only ID. 102 as he has worked at least once in 2016,2017,2018 and 2019 where all the others there is a gap.
I created a new table as you said and took away all the duplicate iDs and built a relationship with the main table. This when I add this to a table I can then drag a column for the years and it shows how many times they have worked over the years
However I want to create a column that shows a 1 against those IDs that have only worked continually for 4 years (so in this case only 102) so I would presumably need to write a formula but I have tried all sorts but as I have duplicates of IDs and years it never works.
I hope this is clearer and thanks for your help.
Hi ham2889,
One sample for your refernce.
1. Create a calculated table as below. Here we needn't create relationship between the new table and the fact table.
Year = VALUES(Table1[Year])
2. Create the measures as below.
Measure =
var _count = CALCULATE(DISTINCTCOUNT(Table1[Year]),ALLSELECTED(Table1),VALUES(Table1[IDs]))
var _dis = CALCULATE(DISTINCTCOUNT('Year'[Year]))
return
IF(MAX(Table1[Year]) in VALUES('Year'[Year]),CALCULATE(DISTINCTCOUNT(Table1[IDs]),FILTER(ALLSELECTED(Table1[Year]),_count=_dis)))
Measure 2 = CALCULATE(DISTINCTCOUNT(Table1[IDs]),FILTER(Table1,[Measure]<>BLANK()))
For more details, please check the pbix as attached.
Regards,
Frank
Hi ham2889
Below is my solution where I created a very small star schema. This is ideal when creating data models and can easily solve your issue as shown below.
v-frfei-msft solution too works, so you can decide which one works for you.
Here is a link to the PBIX: Really need help