Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago

Reference Table from DAX

Hi Guys!!   My requirment is to create one Reference table using DAX in such a way that if there is any change in base table it will also get reflected in Reference table as well.   Lets say, I h...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

amsrivastavaa Sure, use this version:

Selector 2 = 
    VAR __Amount = SUM('Amit'[Amount])
    VAR __TotalAmount = [Total_Amount 2]
    VAR __Percent = DIVIDE(__Amount, __TotalAmount)
    VAR __Range = SELECTEDVALUE('Range-Amit'[Range])
    VAR __Ranges = COUNTROWS(DISTINCT('Range-Amit'[Range]))
    VAR __AllRanges = COUNTROWS(DISTINCT(ALL('Range-Amit'[Range])))
RETURN
    SWITCH(TRUE(),
        __Ranges = __AllRanges,1,
        __Range = "<25%" && __Percent < .25,1,
        __Range = "25-50%" && __Percent >= .25 && __Percent < .5,1,
        __Range = "50-75%" && __Percent >= .5 && __Percent < .75,1,
        __Range = "75-100%" && __Percent >= .75 && __Percent < 1,1,
        0
    )
amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago

Hi Greg_Deckler ,

 

I have updated the SELECTOR 2 with code provided, for most of the records, it works well, i have found just one exception which is detailed below

 

Just to explain, I have selected Type=Credit, Year=2017 and Range =50-75, then it gives me records with Amount as 1000, however 100*(1000/1210 )=82.6 which must be part of 75-100%, so please suggests!!

 

Another requirement I just realised is : Lets say in Tabular View 2 after applying Slicers, I have got record for Project=P-1, then I need to accumulated all the records that belongs to P-1, i.e. all the records of Type = Debit and Type=Credit for Year 2017 (as P-1 belongs to 2017 in this case) and then have to create Matrix on top of that data.

 

To elaborate further, let say, in Tabular View-2, I have got two records after filteration, one for (Project=P-1 and Year=2017) and another (Project=P-2 and Year=2018) then first i need to accumulate all the data (Type=Debit or Type=CREDIT) for (Project=P-1 and Year=2017) and another (Project=P-2 and Year=2018) and then create Matrix visualization.

 

Please suggest!!

 

Thanks

Amit