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
amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago

Hi Greg_Deckler ,

Many Many Thanks for the same, its incredible.

 

I have gone through the PBI and it looks good as of now with only exception.

 

Range slicer works well when I am selecting any value in the Range slicer and subsequent records dispaly in the table, however when I didn't selected anything in the slicer, its not showing any records in the table, on the contrary, if nothing selected in Power BI slicers then means all the values of slicers is part of the result set.

 

Could you please help me on the same as well, i.e.when nothing selected in Range filter, it will not filter anything and complete data will get dispalyed.

 

Thanks

Amit Srivastava

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