slicer does not filter visual

[Anonymous]

I have 2 slicers and 2 cards here. The cards are from different tables from the slicers. But they will only change when I use the YEAR slicer, but not for the PURPOSE. What DAX should I use to make the value change according to the slicer too?

I have tried to create this column in COST Table since the slicer is from the COST Table. But it won't work.

Plan_lookup = LOOKUPVALUE(plan[Count Plan],plan[YEAR],cost[YEAR], plan[Purpose], cost[PURPOSE])

I also tried to change the relationship model but nothing happens. Please know that that I have a lot of tables in my actual pbix so I can't just change the relationship.

This is supposed to be the output.

 

This is my pbix file link.
https://1drv.ms/u/s!AoKh3nlpMpSOglsiwAttrllh1Ryi?e=uLauQf

Thank you in advance

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

If you don't want to change the relationship, you can create measures like this.

Measure = CALCULATE(COUNT(dms[NAME]),FILTER(ALL(dms),dms[YEAR] in VALUES(cost[YEAR]) &&dms[PURPOSE] in VALUES(cost[PURPOSE])))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

I removed Null values on one side.

[amitchandak]

@Anonymous , you need to create a star schema model. Please find the attached file after signature

[Anonymous]

For some of the tables, I cannot create relationship as you show.

Is there another way to solve this?