How to show data according range of values.

[Padagon]

Hi, good afternoon. I 've been struggling with next need.

I have a couple of tables

Category:

idCategory	Category
C1	A
C2	B

Subcategory:

idCategory	idSubcategory	Subcategory
C1	S1	Sub1A
C1	S2	Sub1B
C2	S3	Sub2A

Assess:

id	idCategory	Activity	Score	Subcategory	Level
1	C1	A1	1	Sub1A	Level 1
2	C1	A2	3	Sub1A	Level 2

Level

idCategory	idSubcategory	Level	Description
C1	S1	Level 1	Descr 1
C1	S2	Level 2	Descr 2

I am creating a calculated table 
AVGSubcategory to get the Subcategory grouped, an average score, and a corresponding Level from that average score (there are 5 levels in total, each level gets a score: level 1 from 0 to 1, level 2 from 1 to 2, level 3 from 2 to 3, level 4 from 3 to 4, and level 5 from 4 to 5.

Subcategory	AvgScore	Level
S1	2.6	Level 3
S2	3.5	Level 4

In my dashboard. I am using a TileSlicer to display all Categories, then I am using the new visual Card to display the Subcategories within the selected Category and also display the Average Score from Assess.
Then I am adding a Label to this visual to display the Level according to the sucategory and the avg score. This works fine.

Now, a I am struggling with displayin the Description.

The reason is: each category has 2 subcategories. Each subcategory has a score from 0 to 5, each score has a named value: Level 1, Level 2, Level 3, etc. So, when a subcategory receives an score, an score has a Level (name) and a Description. I'd like to display the description as an additional Label in the new card visual, acccorgding to the score and/or level assigned to the subcategory.

I hope this was clear and you could help. Thanks in advance

[v-karpurapud]

Hi @Padagon 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @Padagon 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @Padagon 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @Padagon 

Thank you for reaching out to Microsoft Fabric Community Forum. Try the following steps to resolve the issue:

1. Calculate the Average Score for each Subcategory from the Assess table.
2. Assign a Level based on predefined score ranges, where Level 1 is for scores 0 to <1, Level 2 for 1 to <2, Level 3 for 2 to <3, Level 4 for 3 to <4, and Level 5 for 4 to 5.
3. Create a Lookup Table that contains Level names and their corresponding Descriptions.
4. Establish a Relationship between the calculated Level and the Lookup Table to fetch the correct description.
5. Add the required visuals in Power BI a Tile Slicer to filter by Category and a Card Visual to show the Subcategory, Avg Score, Level, and Description dynamically.
   
   

If you find this solution helpful, please consider marking it as "Accept as Solution" to assist other community members. If the issue persists, kindly share the PBIX file, allowing us to provide better assistance.

Thank You.

[ABD128]

Hello @Padagon 

If possible could you please share the PBIX file. So that it will be easy to understand what you have done so far and what you left with?

[pankajnamekar25]

Hello @Padagon 

You can create a calculated column in your AVGSubcategory table to fetch the Description from the Level table.

DescriptionColumn =

VAR SelectedLevel = 'AVGSubcategory'[Level]

RETURN

LOOKUPVALUE(

    Level[Description],

    Level[Level], SelectedLevel

)

SelectedLevel gets the calculated Level from AVGSubcategory.

LOOKUPVALUE searches the Level table for the matching Description.

Using Measure  also you can do

If you want it as a measure instead of a calculated column:

DescriptionMeasure =

VAR SelectedSubcategory = SELECTEDVALUE(AVGSubcategory[Subcategory])

VAR SelectedLevel = SELECTEDVALUE(AVGSubcategory[Level])

RETURN

LOOKUPVALUE(

    Level[Description],

    Level[idSubcategory], SelectedSubcategory,

    Level[Level], SelectedLevel

)

Add this column/measure to your new Card visual.

It will dynamically show the Description based on the selected category and subcategory.

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.