Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
@Schmidtmayer , In case you need measure slicer
refer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://www.youtube.com/watch?v=vlnx7QUVYME
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |