How to force a slicer from a table without Data using DAX?

[fmarthidalgo]

Good morning all!

I am struggling with something that might have an easy fix, I just cannot get around it! 

Here is my scenario:

Table A shows the actual results collected, while Table B shows budgted results. The two tables are connected through the ID column. 

Table A 

ID	Year	Month	Territory	Actuals
2024JanuaryNorth	2024	January	North	5000
2024JanuarySouth	2024	January	South	2000
2024FebruaryNorth	2024	February	North	4500
2024FebruarySouth	2024	February	South	2500

Table B 

ID	Year	Month	Territory	Budget
2024JanuaryNorth	2024	January	North	5200
2024JanuarySouth	2024	January	South	2300
2024FebruaryNorth	2024	February	North	4000
2024FebruarySouth	2024	February	South	2500
2024MarchNorth	2024	March	North	5000
2024MarchSouth	2024	March	South	2200

So when I try to create visuals with example above, I must slice my matrixes (Rows) and bar graphs (x-axis) using the 'Table A' [TERRITORY] due to other contraints that I have. 

I used two simple measure to calculate totals:

Measure Actuals =

        CALCULATE(

                 SUM('Table A' [Actuals]),

                 'Table A'[Month] = "March"

        )

Measure Budget =

        CALCULATE(

                 SUM('Table B' [Budget]),

                 'Table B'[Month] = "March"

        )

So Assuming March ended, and there were no recorded actuals for March, I try to present both measures on a bar graph where my x-axis NEEDS to be sliced by TABLE A's Territory column. So, eventhough there are no records on TABLE A for March, I do have a budget on TABLE B for march. I need my graph/matrix to show me TABLE B's budget (ie. 5000 for North Territory) and a 0 for actuals for March.

Could I force this on the graph using a measure? 

Thank you! 

Martin.

[Corey_M]

This should force it to show up by giving it a value. however this will show all values to show up so you might need an additional filter on the x-axis to only show what you want to see

Measure Actuals Continuous =
IF(
   ISBLANK([Measure Actuals]),
   0,
   [Measure Actuals]
)

[fmarthidalgo]

Thank you for your resopnse @Corey_M. So I did try this and March does come out as a 0 for Actuals on the x axis. The problem is that the budget shows as a blank as opposed to 5000. Ideally, what I need is for March Actual to show the 0 with the IF expression you have suggested and March Budget shows 5000. 

[Corey_M · Accepted solution]

if the table in your post is correct, I suspect this is part of your issue.

The other issue you'll have is that since their is no id in Table A to link to in Table B the values in table B will show as a blank territory. you say you have to slice on Table A territory, but is there really no way to use an intermediate dimension table? I got a sample to work, using a dimension table related to A and B instead of relating A to B.

[fmarthidalgo]

I typed the sample tables manually, thus the error you are highlighting. Great catch! In my model the ID column is built with a nested CONCATENATE expresion so it is right. 

I think it does have more to do with the Territory I'm using to slice the matrix/visual not existing due to no Actuals being recorded in March. I'll go ahead and create an intermidiate dimension table, hopefully this will solve the problem! 

Thanks again!