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
Schmidtmayer
Helper I
Helper I

Dynamic Visualisation using Parameters

Hello everydody, having another problem, first of all the structure of the underlying data:

Fact Table:
personal number, int
date, date
time_category, int
time_subcategory int
hours, decimal,
age_class, int,
affiliation_category int,
weekly_hours int

Then there are different dimension tables, all connected to the fact table with a bidirectional relationship, all active:

DimEmployees (connected via personal number)
personal number, int
name text

DimDates (connected via date)
date, date
some other datefacts, seems kinda irrelevant to me

DimAgeClasses (connected via age_class and age_class_id)
age_class_id, int
range_of ages, text (like "18-25")

DimAffiliationCategories (connected via affiliation_category_id and affiliation_category)
affiliation_category_id int,
affiliation_category_name text

Then, there is already a parameter involved, consisting of a single-columned table containing all number from 0 to 100, the table is called parameter, the column is called limit and a corresponding measure Choice = SELECTEDVALUE(parameter[limit], 9)

Then, there are 3 measures calculating all needed KPI's, in this context there are:

1) NumberOfEmployees
2) IllnessQuota
3) IllnessRate (in contrast to the IllnessQuote, the IllnessRate returns how many % of the employees have an IllnessQuota >= Choice, here the first parameter comes into play)

All of these measures work fine.

Til now, there is a report with 3 pages, one analysis for age class, one for affiliation_category, one for weekly_hours (there are even more, just want to cut the problem). My wish is the following: Use parameter in a way, that the user can choose which property (age_class, affiliation_category, weekly_hours) is displayed via filter.

My idea was:

1) Create a single-columned table criteria_list with the column criteria. Thse will appear for the user as choices in the filter. There are 3 values, all text:
Age Class,
Weekly Hours,
Affiliation Category

2) Create a measure to return the selected value by the user: Choosen = SELECTEDVALUE(criteria_list[criteria], "Weekly Hours")

3) Create a calculated column in the fact table, returning the corresponding values from the dimension tables, based upon the value of Choosen:

SwitchRow = IF(
    [Choosen] = "Age Class", RELATED(DimAgeClasses[range_of ages]), IF(
        [Choosen] = "Weekly Hours", CONVERT(weekly_hours, string), IF(
            [Choosen] = "Affiliation Category", RELATED(DIMAffiliationCategories[affiliation_category_name]), 
            RELATED(DimAgeClasses[range_of ages])
            )
        )
    )


With this, the task should be doable. Just choose clustered column chart as visualisation type, put Year and Month from the dates table into the axis, put SwitchRow as legend and the measure you wish to visualize as values. Done.

My result: The visualisation always show the return value when false is returned by the inner IF-block, in this case RELATED(DimAgeClasses[range_of ages]), this happens even if that block is changed.

So my question: What am I missing here? All the names in SwitchRow are spelled correctly, I checked.




3 REPLIES 3
v-diye-msft
Community Support
Community Support

Hi @Schmidtmayer 

 

Could you please kindly provide the dummy pbix that we can investigate it further?

Please upload your dummy pbix with expected results disclosed to the Onedrive for business, then share the link here.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Hi @amitchandak ,

thanks for the reply.
The first post is actually describing what I already try to do.
My question is about troubleshooting, not the general concept.
The second post seems kinda unrelated, the video was really interesting, but also not helpful.

Greetings Schmidtmayer

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.