Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm spending much time on doing something I've already done with numbers and now I 'm struggling to do it with text values.
I have a table (Records_Africa) with two columns, [AGILITY SCORE] and [Month].
[AGILITY SCORE] contains text values such as "WARNING", "EXCELLENT", "ALERT".
In my report, a date slicer uses [Month] column and this slicer is filtered by the user.
There is also a Card visual that displays Agility Score.
I want this card visual to display only LATEST value of AGILITY SCORE, within the Month slicer selection made by the user.
How can I do that please ?
PS : With numbers, formula works, it is :
Solved! Go to Solution.
Replace the AVERAGE() with a SELECTEDVALUE()?
Measure = VAR maxDate = MAX('Records_Africa'[Month]) Return CALCULATE(SELECTEDVALUE('Records_Africa'[Agility Score]); 'Records_Africa'[Month] = maxDate )
But you will have an issue if there are more than 1 agility score on the same date.
Just try MAX instead of LASTDATE
False.
The field is not numbers but text.
I think we should replace CALCULATE AVERAGE by smth else but I don't know how.
hi, @Mype_powerbi
You need to add a map calculated column for [Month] for calculation or add a dim date table then create the relationship with basic data by month, and then use it for calculation.
Best Regards,
Lin
Hello @v-lili6-msft ,
Thanks for answering me.
Ok, I've added another table with Date dimension called Time.
What do I have to do now ?
Replace the AVERAGE() with a SELECTEDVALUE()?
Measure = VAR maxDate = MAX('Records_Africa'[Month]) Return CALCULATE(SELECTEDVALUE('Records_Africa'[Agility Score]); 'Records_Africa'[Month] = maxDate )
But you will have an issue if there are more than 1 agility score on the same date.
thank you for helping me @tex628 .
Unfortunatly this doesn't work for me 😞
There is only one Agility Score by Month, so it should be fine.
The visual displays "Blank" with your measure. Maybe because several months are selected in the Month Slicer ?
Use
Measure = VAR maxDate = MAX('Records_Africa'[Month]) Return CALCULATE(DISTINCTCOUNT('Records_Africa'[Agility Score]); 'Records_Africa'[Month] = maxDate )
and show me the result. Need to figure out what the issue is 🙂
Alright, from what i can tell there appears to be more than 1 agility score for a single month.
To make sure do the following:
Create a new table.
Add the following columns:
* Month
* Agility score
Filter on ONE month.
Take a picture of the table and the filter! 🙂
Ok it works !!!
Now that I really have 1 Agility Score Value by Month, I put your measure :
Hello @tex628
You are right.
There is more than 1 Agility Score per month.
I need to fix that and then I will use your formula.
Which one is supposed to work ?
Many thanks, you help me so much !
Once it works I will tag your answer as Accepted Solution.
Augustin
hi, @Mype_powerbi
Create the relationship with [Month] column, then use date field from this dim table for report.
If possible, please share your sample pbix file and your expected output and I could test on it.
Best Regards,
Lin
Hi @v-lili6-msft ,
Please find here the link to my pbix file "Essec_Training_File_test".
https://drive.google.com/file/d/185cQPatOToBxKsnr-J_bFclWHp-UBXRb/view?usp=sharing
Visual AGILITY SCORE must display values like here :
Best regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |