Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
So I have 3 columns in 1 table. The table is Billing and the three columns are Id, Date, Insurance Name.
Id | Date | Insurance Name |
100 | 8/15/2018 | Aetna |
100 | 8/16/2018 | Aetna |
101 | 8/18/2018 | Fidelis |
102 | 8/25/2018 | Fidelis |
102 | 8/15/2018 | Healthfirst |
102 | 7/15/2018 | Healthfirst |
102 | 8/10/2018 | Healthfirst |
103 | 8/6/2018 | Aetna |
103 | 8/9/2018 | Aetna |
103 | 8/15/2018 | Healthfirst |
104 | 8/24/2018 | Fidelis |
104 | 8/30/2018 | Fidelis |
I want to only include the values for the max date and min date. I have alot of values for a specific Id, some have like 6 seven fields and I want to do some basic calculations and so only need two date values, the max and min. For examply the original table will now show only these rows:
Id | Date | Insurance Name | max or min |
100 | 8/15/2018 | Aetna | min |
100 | 8/16/2018 | Aetna | max |
101 | 8/18/2018 | Fidelis | max |
102 | 8/25/2018 | Fidelis | max |
102 | 7/15/2018 | Healthfirst | min |
103 | 8/6/2018 | Aetna | min |
103 | 8/15/2018 | Healthfirst | max |
104 | 8/24/2018 | Fidelis | min |
104 | 8/30/2018 | Fidelis | max |
Solved! Go to Solution.
Hi @saanah2019
You may create a measure like below:
Measure = VAR min_date = CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Id] ) ) VAR max_date = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Id] ) ) RETURN IF ( MAX ( Table1[Date] ) = max_date, "max", IF ( MAX ( Table1[Date] ) = min_date, "min" ) )
Regards,
What determines if you want to see the min or the max?
@Anonymous I want to basically filter on the date, so basically create a new column which willl only show me the min and max dates
Hi @saanah2019
You may create a measure like below:
Measure = VAR min_date = CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Id] ) ) VAR max_date = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Id] ) ) RETURN IF ( MAX ( Table1[Date] ) = max_date, "max", IF ( MAX ( Table1[Date] ) = min_date, "min" ) )
Regards,
User | Count |
---|---|
98 | |
90 | |
83 | |
69 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |