Forum Discussion

Anshenterprices's avatar
Anshenterprices
Icon for Helper IV rankHelper IV
5 years ago
Solved

dynamically change the values based on the slicer selection

Hi Team, source data Universityid Ranks CollegeName Category SubCategory A1 1 Cape Town Oxford Andrews B2 2 Pretoria Oxford Harvard  A2 3 Witwatersrand Stanford  Calte...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anshenterprices 

     

    Here is one way

     

    The fact table is like your sample, and the dim table as below, NO Relationship between them, Slicer is using dim table

     

    Result = 
VAR SelectedU = SELECTEDVALUE(dimU[Universityid])
VAR CurU=MAX(SourceTable[Universityid])
VAR T1=FILTER(ALL(SourceTable),SourceTable[Universityid]=SelectedU)
VAR CurSub = MAXX(T1,SourceTable[SubCategory])
VAR CurCat=MAXX(T1,SourceTable[Category])
RETURN
SWITCH(TRUE(),
CurU=SelectedU,MAX(SourceTable[CollegeName]),
CurSub in VALUES(SourceTable[SubCategory]),CurSub,
CurCat in VALUES(SourceTable[Category]),CurCat,
"General")

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anshenterprices 

 

Here is one way

 

The fact table is like your sample, and the dim table as below, NO Relationship between them, Slicer is using dim table

 

Result = 
VAR SelectedU = SELECTEDVALUE(dimU[Universityid])
VAR CurU=MAX(SourceTable[Universityid])
VAR T1=FILTER(ALL(SourceTable),SourceTable[Universityid]=SelectedU)
VAR CurSub = MAXX(T1,SourceTable[SubCategory])
VAR CurCat=MAXX(T1,SourceTable[Category])
RETURN
SWITCH(TRUE(),
CurU=SelectedU,MAX(SourceTable[CollegeName]),
CurSub in VALUES(SourceTable[SubCategory]),CurSub,
CurCat in VALUES(SourceTable[Category]),CurCat,
"General")