Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Month | End of Month | Type | Key | Value |
Mar-2021 | 2021-03-31 | 1 | Key1 | High |
Mar-2021 | 2021-03-31 | 2 | Key2 | Low |
Mar-2021 | 2021-03-31 | 3 | Key3 | Low |
Feb-2021 | 2021-02-28 | 1 | Undefined | Undefined |
Feb-2021 | 2021-02-28 | 2 | Undefined | Undefined |
Feb-2021 | 2021-02-28 | 3 | Key3 | Low |
Jan-2021 | 2021-01-31 | 1 | Undefined | Undefined |
Jan-2021 | 2021-01-31 | 2 | Key2 | Medium |
Jan-2021 | 2021-01-31 | 3 | Key3 | Medium |
Dec-2020 | 2020-12-31 | 1 | Key1 | Low |
Dec-2020 | 2020-12-31 | 2 | Key2 | High |
Dec-2020 | 2020-12-31 | 3 | Key3 | High |
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
Key | Value |
Key1 | High |
Key2 | Low |
Key3 | Low |
Last selected date falls in Feb-2021
Key | Value |
Key1 | Low |
Key2 | Medium |
Key3 | Low |
(Took value for Key1 from December)
(Took value for Key2 from January)
Last selected date falls in Jan-2021
Key | Value |
Key1 | Low |
Key2 | Medium |
Key3 | Medium |
(Took value for Key1 from December)
Can you please help me in doing thist task?
Thank you.
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi, @Anonymous
Please check the below picture and the sample pbix file's link down below.
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,
You may download my PBI file from here.
Hope this helps.
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.
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.
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |