Reference Table from DAX

[amsrivastavaa]

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 have one Filter F-1 and One Table visual Table-1, let say User saelected any value in F-1 and data got filtered in Table-1, i need to create Table-2 that refrenced Table-1, that is as soon as F-1 filtered the Table-1, Table-2 also get filtered and always have same set of data as Table-1 has.

Please suggest how can  we do this via DAX

Thanks

Amit Srivastava

[mangaus1111]

Hi @amsrivastavaa ,

try this maybe it works

Total_Amount = CALCULATE(SUM('Facts17'[Amount]),ALLSELECTED(Facts17))

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

[amsrivastavaa]

Hi @mangaus1111 

Thats giving me sum of all the records, i need sum based on Type and year for whatever data left after applying filter.

Thanks

Amit

[Greg_Deckler]

@amsrivastavaa You cannot. DAX calculated columns and tables are not dynamic, they are calculated at the time of refresh.

[amsrivastavaa]

Hi @Greg_Deckler ,

Can we have something like this using DAX as detailed below.

Let say I have table Transaction as shown below

Project	Type	Year	Amount	Total_Amount
P-1	A	2017	100	160
P-2	A	2017	50	160
P-3	A	2017	10	160
P-1	A	2018	20	75
P-2	A	2018	55	75
P-1	A	2019	75	250
P-2	A	2019	85	250
P-3	A	2019	90	250

Project, Type, Year & Total_Amount is coming from Source however for Total_Amount i have created using below DAX

Total_Amount = CALCULATE(SUM('Transaction'[Amount]),ALLEXCEPT('Transaction','Transaction'[Type],'Transaction'[Year]))

This works well when we have complete set of data but when user selects say Project=P-1 in slicers , then amount of all other Project P-2, P-3 still added in Total_Amount.

I want, when user selects say P-1, then it will only sum amount for P-1 project for Total_Amount measure, similarly, if User Selects P-2 and P-1 both, Total_Amount must be sum of all the amounts of Project P-1 and P-2.

Based on above table.

If user select Project= P-1, Year=2017 then  Total_Amount = 100

If user select Project= P-1 and P-2, Year=2017 then  Total_Amount = 100+50 = 150.

If user select Project= P-1 ,P-2 and P-3, Year=2017 then  Total_Amount = 100+50 +10= 160

Can we define measure (tot_amount) in such a way that it will work on data which is available after filteration.

Thanks

Amit 

[Greg_Deckler]

@amsrivastavaa That should be possible:

Total_Amount = CALCULATE(SUM('Transaction'[Amount]),ALLEXCEPT('Transaction','Transaction'[Type],'Transaction'[Year],'Transaction'[Project]))

[amsrivastavaa]

Hi @Greg_Deckler ,

Below is the layout of the data 

Tot_Amount = 

Total_Amount = CALCULATE(SUM('Transaction'[Amount]),ALLEXCEPT('Transaction','Transaction'[Type],'Transaction'[Year])

Total_Amount = 

Total_Amount = CALCULATE(SUM('Transaction'[Amount]),ALLEXCEPT('Transaction','Transaction'[Type],'Transaction'[Year],'Transaction'[Project]))

Filter available : Project

First Instance : When user have selected all the Project, then Total sum for Credit across all the project for Year 2017 is 1210 i.e. Tot_Amount value.

Second Instance : When user have selected Project as P-1 and P-2 only, as shown below

Then, Total Sum of Amount for Credit across all the project for Year 2017 must be 1000 +100=1100.

But  none of the above DAX gave me correct result.

I need DAX which will calculate Total Amount based on what data available after filteration

Please suggest!!

Thanks

Amit 

[Greg_Deckler]

@amsrivastavaa See attached PBIX file below signature. The measure seems to work for both of your scenarios. Returns 1210 if just Year = 2017 and 1,100 if P1 and P2 and Year = 2017. You have to use a measure, you cannot use a column.

[amsrivastavaa]

Hi @Greg_Deckler ,

Reason, why I want to have it as a column is

- I want to filter transaction table based on this column.

- Whatever data left after applying filter, I have to create Matrix visual for that data.

So, if I will create measure for this,  I will not be able to filter the data and subsequently not able to create Matrix visual.

ANy thought or work around??

Thanks

Amit

[Greg_Deckler]

@amsrivastavaa You can filter a visual using a measure. I can't exactly picture what you are trying to do here in terms of your matrix. Can you provide a mock-up? 

The only way to do this using calculated columns is you would have to create a column for every possible combination of what users might select/filter and that's just not a feasible solution. I'm pretty certain there is a way to get what you want using measures, but I need to understand what you are really trying to accomplish because it is not clear to me.

[amsrivastavaa]

Hi @Greg_Deckler 

I have placed the PBI on onedrive, please find on the below URL

https://1drv.ms/u/s!Ahtm7otFIxr8cX5JDN5SEc0Vajw?e=xREfsH

As a brief, below are the requirement detail

Requirement

User wants to see Matrix report only for those project where AMOUNT/Total AMOUNT is less than 25% (if user selected <25% in Range slicer).

Other details are available in PBI.

Thanks

Amit

[Greg_Deckler]

@amsrivastavaa See attached PBIX below signature. Wish we would have started with the real requirement. You basically need a complex selector. 

[amsrivastavaa]

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]

@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]

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