Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abogdanov
Helper I
Helper I

Assigning value to every row of the ID

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 idStatusDate changeApplication Status
1Applied01/01/2020 
1Processing01/02/2020 
1Not eligible01/05/2020Inactive
2Applied03/02/2020 
2Processing03/03/2020 
2Verification03/04/2020 
2Approved03/04/2020Inactive
3Applied02/06/2020 
3Processing02/07/2020

Active

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

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:  

v-yalanwu-msft_0-1621235629357.png

If you want to filter out inactive applications

v-yalanwu-msft_1-1621235629358.jpeg

 

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.

Jihwan_Kim
Super User
Super User

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.

 

Picture4.png

 

Application Status Measure =
VAR currentid =
MAX ( 'Table'[Customer id] )
VAR maxdate =
CALCULATE (
MAX ( 'Table'[Date change] ),
FILTER ( ALL ( 'Table' ), 'Table'[Customer id] = currentid )
)
VAR allstatus =
CALCULATETABLE (
VALUES ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
'Table'[Customer id] = currentid
&& 'Table'[Date change] = maxdate
)
)
VAR statuscheck =
COUNTROWS ( INTERSECT ( allstatus, { "Not eligible", "Approved" } ) )
RETURN
IF (
ISFILTERED ( 'Table'[Customer id] ),
IF ( statuscheck >= 1, "Inactive", "Active" )
)
 
 
Status Count Measure =
SUMX (
VALUES ( 'Group'[Group] ),
CALCULATE (
COUNTROWS (
FILTER (
VALUES ( 'Table'[Customer id] ),
[Application Status Measure] = SELECTEDVALUE ( 'Group'[Group] )
)
)
)
)
 
 
 
 

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.


Go to My LinkedIn Page


Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, you may right click on the Application status column and select Fill > Up.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.