Filter from disconnected table

[SHARKBIRD]

Hi!

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

Date	Orign	Version	Value
1/1-2019	Actual		1.000,00
1/1-2019	Budget	A	900,00
1/1-2019	Budget	A	150,00
1/1-2019	Budget	B	1.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].

[Anonymous]

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

[ibarrau]

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]

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 🙂

[mahoneypat · Accepted solution]

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]

Hey Pat

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