Forum Discussion
Helper IVdynamically 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 | Caltech |
| B3 | 4 | Rhodes | Oxford | Andrews |
| A3 | 5 | Mansoura | Cambridge | Harvard |
output table with slicer of university id
| Universityid | Ranks | Calculated column |
| A1 | 1 | Cape Town |
| B2 | 2 | Oxford |
| A2 | 3 | General |
| B3 | 4 | Andrews |
| A3 | 5 | General |
once user select A1 university id from the slicer table should diplay all the rows only change is calculated column.
dax:- if user select A1 from slicer then college name
else A1 subacategory match with other university id and if it is match then take the subcategory of A1
else A1 category match with other university id and if it is match then take the category of A1
then result will looks like in the above format.
Please guide me, with table relationship a
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")
1 Reply
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")