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
Anonymous
Not applicable

Get the most recent non-blank values for each type separately in a table visual

Hi,

 

I have a table like this. In this table, there are three keys for each month. Each key can be one of High, Medium, or Low. But key and value can also be Undefined, if they are not defined for that month.

 

MonthEnd of MonthTypeKeyValue
Mar-20212021-03-311Key1High
Mar-20212021-03-312Key2Low
Mar-20212021-03-313Key3Low
Feb-20212021-02-281UndefinedUndefined
Feb-20212021-02-282UndefinedUndefined
Feb-20212021-02-283Key3Low
Jan-20212021-01-311UndefinedUndefined
Jan-20212021-01-312Key2Medium
Jan-20212021-01-313Key3Medium
Dec-20202020-12-311Key1Low
Dec-20202020-12-312Key2High
Dec-20202020-12-313Key3High

 

I want to show the last three Key and Value in a table visual. However, if key and value are Undefined, it should take the key and value from a previous month, the most recent which is not blank.

 

So, for the above table, the outcome of the table visual in different situations is:

 

Last selected date falls in Mar-2021

KeyValue
Key1High
Key2Low
Key3Low

 

Last selected date falls in Feb-2021

KeyValue
Key1Low
Key2Medium
Key3Low

(Took value for Key1 from December)

(Took value for Key2 from January)

 

Last selected date falls in Jan-2021

KeyValue
Key1Low
Key2Medium
Key3Medium

(Took value for Key1 from December)

 

 

Can you please help me in doing thist task?

Thank you.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

7 REPLIES 7
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

 

Picture2.png

 

Key Measure =
VAR currenttype =
MAX ( 'Values'[Type] )
RETURN
IF( ISFILTERED( 'Values'[Type]),
CALCULATE (
LASTNONBLANKVALUE (
'Values'[Date],
CALCULATE ( SELECTEDVALUE ( 'Values'[Key] ), 'Values'[Key] <> "Undefined" )
),
FILTER (
ALL ( 'Values' ),
'Values'[Date] <= MAX ( Dates[Date] )
&& 'Values'[Type] = currenttype
)
)
)
 
 
Value Measure =
VAR currenttype =
MAX ( 'Values'[Type] )
RETURN
IF( ISFILTERED( 'Values'[Type]),
CALCULATE (
LASTNONBLANKVALUE (
'Values'[Date],
CALCULATE ( SELECTEDVALUE ( 'Values'[Value] ), 'Values'[Key] <> "Undefined" )
),
FILTER (
ALL ( 'Values' ),
'Values'[Date] <= MAX ( Dates[Date] )
&& 'Values'[Type] = currenttype
)
)
)
 
 
 

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,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

Hi @Ashish_Mathur 

 

In a similar situation, imagine I want to show all recent non-blank "values" in a card visual. So, only "values", not keys.

With the current measure, it only shows a single one in the card.

I tried to modify your measure to achieve this, but wasn't successful.

Can you please help?

Hi,

Based on the data that you shared in your initial post, show the exact result you are expecting.


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

Just want to show only the value column of what you did, in a multi-row card visual.

 

If we know that first row to third row are Key1 to Key3:

 

Last selected date falls in Mar-2021:

 

High

Low 

Low

 

Last selected date falls in Feb-2021

 

Low

Medium

Low

 

(Took value for Key1 from December)

(Took value for Key2 from January)

 

Last selected date falls in Jan-2021

 

Low

Medium

Medium

 

(Took value for Key1 from December)

 

I guess the output data should be in a table format so that it is visualised correctly in the multi-row card. But, I could not modify your code to achieve this. 

Looking forward to your help.

Thanks 

Hi,

See if this works.  Download the PBI file from here.

Untitled.png


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

Hi,

For my formula (shared with you already in the PBI file), there must bea row context of keys.  In a card visual, since there would not be a key column (and therefore no row context), I am at a loss to solve this question.  May be someone else will help you.


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.