I want to show all businesses from 1 to 10 in column 1 irrespective of the slicers and the slicers should only filter the 2nd column and 3rd column.
For example - when month 1 is selected in the slicer, business 8 should show a 0 or null in the 2nd column and 3000 in the 3rd column
Test.pbix (60.3 KB)
I feel I am missing something in the DAX- any insights on this from the experts would be helpful.
Thank you.
Few very important things here.
You need to make sure you use much better naming conventions on your tables. Maybe this is just because it’s demo data but it also make it difficult to understand what I’m looking at.
…Especially in the model here. This model doesn’t really make sense to me at all and is likely that very reason why you are also confused.
When you are unable to understand what slicer and filters are doing in your report it almost always comes done to not understanding what is happening in the model.
You need to be able to almost visualize what’s happening in your model when any ‘context’ is applied to your calculations in your reports.
The slicer here are always going to slicer the table unfortunately as that is placing a real filter on the table in your model.
If you want to show all the results you will need to work this differently in your model.
For example you will need a separate month table which has no relationship to this main one.
Now you DAX formulas have to integrate this month table into them.
3Value =
sumx( Table3,
IF( Table3[Month] = SELECTEDVALUE( Months[Month] ), Table3[Value], 0 ))
I’ll attach.
Test - EDNA.pbix (68.1 KB)
See how you go with your other calculations now that you know the strategy required to solve for this.
Sam
Thank you. This works.
You are right that I have to better organize the modelling piece and also the naming. I put this together in a rush and yes I should not have.
