Hi everyone hope someone can help.
I have a fact table called kpi_fact_targets this then has a dimension table of kpi_dim_metric. (with the fields displayed below.) I also have a table called organisation which filters fact table. On my page there is a slicer which allows you to select an organisation. Then number of card visuals. On the filter pane of a card visual I have put in metric_id from the fact table. This should then tell that card what to display. I then wish to populate this card visual with a number of things. All pulled from match metric_id. Title of card, Sub title, also separate card showing target for the KPI. While the KPI itself is calculated else where from other data.
Now at this point I need to point out that not all KPI apply to all organisation. Therefor some don't have a entry within fact table that matches one set in the filter pane. However we will still need to pull the title, subtle, through as a value for that KPI will be shown but with no Target.
I'm trying to write a measure for each of the above. If there is match in target table it's fine. it pulls the correct match. However if a particular organisation does not have that KPI. It finds blank for title, sub title and target. I assumed I could just do match of the value set in the filter pane against metric_id in the dim table. However this does work. If the kpi_fact_table is filter and subsequently does not contain the metric_id set on the filter pane. Even though that field has been set. is there anyway to access the choice form the filter pane? I've tried selected value but that stops if there is no match in the fact table despite not looking for match there only in the dim table
(unfortunately it's not easy to supply a file due size and complexity of dataset)
kpi_fact_targets
Metric_id
Region_id
Title_id
Target
StartDate
EndDate
KPI_Dim_Metric
(this table has a 1 to many link with fact table)
Metric_id
Metric_Title
Metric_SubTitle
Metric_ Description
