Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a customer application table of the below format and what I am trying to figure out is how to make application status fill every row for each customer and not just one.
The application status is calculated as follows: it looks at max(date change) and what corresponding status it has. If it's IN {'Not Eligible', 'Approved'}, then the application status is Inactive. Otherwise, Active.
My question is how do I make that application status fill all rows for each customer? so in this example, customer id 1 would have application status "inactive" in each of the 3 rows it has. The purpose of it is to be able to filter out inactive applications completely instead of the row that has it
Customer id | Status | Date change | Application Status |
1 | Applied | 01/01/2020 | |
1 | Processing | 01/02/2020 | |
1 | Not eligible | 01/05/2020 | Inactive |
2 | Applied | 03/02/2020 | |
2 | Processing | 03/03/2020 | |
2 | Verification | 03/04/2020 | |
2 | Approved | 03/04/2020 | Inactive |
3 | Applied | 02/06/2020 | |
3 | Processing | 02/07/2020 | Active |
Hi @abogdanov ,
You could create two measures by the following formula:
flag =
VAR _date =
CALCULATE (
MAX ( [Date change] ),
FILTER ( ALL ( 'Table' ), [Customer id] = MAX ( [Customer id] ) )
)
VAR _a =
IF ( MAX ( [Date change] ) = _date, MAX ( [Status] ) )
RETURN
IF ( _a IN { "Not eligible", "Approved" }, 1 )
Application Status =
VAR _d =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), [Customer id] = MAX ( [Customer id] ) && [flag] = 1 ),
[Customer id]
)
RETURN
IF ( MAX ( [Customer id] ) IN _d, "Inactive", "Active" )
The final output is shown below:
If you want to filter out inactive applications
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @abogdanov
Please check the below picture and the sample pbix file's link down below.
In order to create a more dynamic report, I suggest creating a measure like below.
All measures are in the sample pbix file.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
In the Query Editor, you may right click on the Application status column and select Fill > Up.
Hope this helps.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |