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 very basic question that I am not able to find the answer to here.
So I will have one table that i need to look at and it will have a specific id, then the insurance name and a date of transaction for that visit id.
I want to create a new column which looks at the visit ids and makes sure that it is looking at the visit id and if they are the same,
- 1. then it checks the insurance names, if they are the same, both are called "Paid" in new column,
-2. If the insurance names are different then the one with the 'lower date' is 'not paid' and higher date is 'paid'
visit id | insurance | date | status |
100 | aetna | 1/1/2018 | not paid |
100 | fidelis | 1/2/2018 | paid |
101 | fidelis | 1/3/2018 | paid |
101 | fidelis | 1/4/2018 | paid |
102 | cigna | 1/5/2018 | paid |
103 | healthfirst | 1/6/2018 | not paid |
103 | cigna | 1/7/2018 | paid |
103 | cigna | 1/8/2018 | paid |
Solved! Go to Solution.
Hi @saanah2019,
Try this for your calculated column, where Table1 is the table you show:
NewColumn_Status = VAR _NumberOfInsurers = CALCULATE ( DISTINCTCOUNT ( Table1[insurance ] ); ALLEXCEPT ( Table1; Table1[visit id] ) ) RETURN IF ( _NumberOfInsurers = 1; "paid"; IF ( _NumberOfInsurers > 1; IF ( CALCULATE ( MIN ( Table1[date] ); ALLEXCEPT ( Table1; Table1[visit id] ) ) = Table1[date]; "not paid"; "paid" ) ) )
Hi @saanah2019,
Try this for your calculated column, where Table1 is the table you show:
NewColumn_Status = VAR _NumberOfInsurers = CALCULATE ( DISTINCTCOUNT ( Table1[insurance ] ); ALLEXCEPT ( Table1; Table1[visit id] ) ) RETURN IF ( _NumberOfInsurers = 1; "paid"; IF ( _NumberOfInsurers > 1; IF ( CALCULATE ( MIN ( Table1[date] ); ALLEXCEPT ( Table1; Table1[visit id] ) ) = Table1[date]; "not paid"; "paid" ) ) )
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |