Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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" ) ) )
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |