Forum Discussion
Help with slicer and measures with DAX (maybe)
Hi All
I hope you can help me with my problem. I'm still relatively new to Power BI even if I've been lurking here for a while so please bear with me.
Context:
My model have a table that contains Department and Location columns and another survey results table that is joined by location.
The department column contains two departments (Farming and Processing) while my location column have (Cashew Farming, Hazelnuts Farming, Hazelnut Processing, Cashew Processing, Pistachio, Processing etc.).
I am using calculated measures from survey results to give me a percentage figures that I show in a table (for example the one below). I am also using a slicer that uses the departments (Farming and Processing).
Problem:
What I am trying to do now is if I select any department from the slicer (Farming for example), I would like the values to show only the ones for Cashew Farming, Hazelnut Farming, Pistachio Farming and if I change the selection to Processing, it will only show values for Cashew Processing, Hazelnut Processing, Pistachio Processing.
| Location | Great | Good | Bad |
| Cashew | xxx | xxx | xxx |
| Hazelnut | xxx | xxx | xxx |
| Pistachio | xxx | xxx | xxx |
I hope that makes sense? I have a feeling that this is a DAX solution but I'm not sure where to start.
Appreciate your help in advance!
Thank you.
Hi Christoph
All good. I found out that it was a very simple fix - I just had to add another "Actual Location" column in my locations table. So that fixed up my slices and now it's showing the correct figures when they switch department selections. 🙂
Department Location Actual Location Farming Cashew Farming Cashew Farming Hazelnut Farming Hazelnut Farming Pistachio Farming Pistachio Processing Cashew Processing Cashew Processing Hazelnut Processing Hazelnut Processing Pistachio Processing Pistachio Thanks for helping out tho!
6 Replies
- ChrisMuthmann
Resolver II
Hi!
If you have different tables and want to filter one by the other, you have to create a relationship in the tabular model first.From your explanation I believe you have three tables, one fact (survey) and two dimensions (department, lodation). So you have to create a relationship between the fact and the dimension tables. Typically you will have a department_id and location_id in the fact table as a foreign key column.
HTH!
Kind regards,
Christoph
Hi Christoph
Thanks for taking the time to reply to me thread.
Just to clarify, I only have two tables in my model - one for survey results and another one named locations that have two columns (department and location). The third is a "visualisation table" but I used different cards and arranged them to look like a table.
I guess what i'm trying to do here is if the selection in the Department slicer changes, the figures in the "visualisation table" will only show the figures related to that department.
For example, if they choose Farming as department, the figures for Cashew Farming, Hazelnut Farming and Pistachio Farming will show in the "visualisation table". And when they choose Processing as department, the results related to Cashew Processing, Hazelnut Processing and Pistachio Processing will apply.
I hope that makes it clearer?
I guess my issue here is that
- ChrisMuthmann
Do you have a relationship from your fact table (survey) to the junk dimension (locations)?
Yes, there's a relationship between the two tables.
- ChrisMuthmann
I'm sorry, but I just have no idea what could be wrong.
Maybe you could post the definition of the calculated measures or a screenshot of your Power BI Desktop Model view?Hi Christoph
All good. I found out that it was a very simple fix - I just had to add another "Actual Location" column in my locations table. So that fixed up my slices and now it's showing the correct figures when they switch department selections. 🙂
Department Location Actual Location Farming Cashew Farming Cashew Farming Hazelnut Farming Hazelnut Farming Pistachio Farming Pistachio Processing Cashew Processing Cashew Processing Hazelnut Processing Hazelnut Processing Pistachio Processing Pistachio Thanks for helping out tho!
