Forum Discussion

SHARKBIRD's avatar
SHARKBIRD
Regular Visitor
6 years ago
Solved

Filter from disconnected table

Hi!

 

I have a fact table with both actual numbers, and budget numbers like the one below.

 

DateOrignVersionValue
1/1-2019Actual 1.000,00
1/1-2019BudgetA900,00
1/1-2019BudgetA150,00
1/1-2019BudgetB1.125,00

 

Beside that table, i've got a disconnected table with the distinct budget versions;

 

Budget version
A
B

 

I would now like to create a measure;

SUM([Values])

 

... but only for the rows where FACT[Version] is equal to the selected/filtered value in 'Budget versions[Version].

 

 

 

 

 

 

  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Here is one way.

     

    Selected Budget = calculate(sum(FactTable[Value]), treatas(values(Disconnected[Budget Version]), FactTable[Version]))

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Here is one way.

     

    Selected Budget = calculate(sum(FactTable[Value]), treatas(values(Disconnected[Budget Version]), FactTable[Version]))

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • SHARKBIRD's avatar
      SHARKBIRD
      Regular Visitor
      6 years ago

      Hey Pat

       

      That's so awesome - it worked exactly as i hoped for! TREATAS is a new favorite for now 🙂

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Hi. I will assume your real case is different because right now you can just add Version from Fact table to slicer and got the result. Anyway. If there are not so many values you can use Switch like this

    Value of target = 
SWITCH(
    SELECTEDVALUE(Budget[Budget Version];0);
    "A"; CALCULATE( SUM('Fact'[Value]) ; 'Fact'[Version] = "A" );
    "B"; CALCULATE( SUM('Fact'[Value]) ; 'Fact'[Version] = "B" );
    SUM('Fact'[Value])
)

    Check it out running:

    daxSwitchFilter 

    Hope this helps

    • SHARKBIRD's avatar
      SHARKBIRD
      Regular Visitor
      6 years ago

      Hey Ibarrau!

       

      Thanks for the shot! I actually came up to your solution aswell, but as i didn't know which entries that will appear in the Version column, i would like to avoid a "hardcoded" solution 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    The version given by ibarrau  can be expressed in a much more succinct way:

    CALCULATE(
	SUM('Fact'[Value]),
	'Fact'[Version] = 
		SELECTEDVALUE(Budget[Budget Version])
)

    Best

    D