Forum Discussion

fmarthidalgo's avatar
fmarthidalgo
Frequent Visitor
2 years ago
Solved

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

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 

IDYearMonthTerritoryActuals
2024JanuaryNorth2024JanuaryNorth5000
2024JanuarySouth2024JanuarySouth2000
2024FebruaryNorth2024FebruaryNorth4500
2024FebruarySouth2024FebruarySouth2500

 

Table B 

IDYearMonthTerritoryBudget
2024JanuaryNorth2024JanuaryNorth5200
2024JanuarySouth2024JanuarySouth2300
2024FebruaryNorth2024FebruaryNorth4000
2024FebruarySouth2024FebruarySouth2500
2024MarchNorth2024MarchNorth5000
2024MarchSouth2024MarchSouth2200

 

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.

General Comment
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      fmarthidalgo
      Frequent Visitor
      2 years ago

      Thank you for your resopnse Anonymous. 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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.