Forum Discussion

nssidhu74's avatar
nssidhu74
Frequent Visitor
6 years ago

Filtering Disconnected Tables based on selection

I am using Direct Query

 

1. I have a view that uses the following Query

Select Distinct(SourceCountry),
( Select MAX(Data_date) from FactInventory FI Where FI.SourceCountry = I.SourceCountry) AS MaxDate,
      ( Select Sum(Volume) from FactInventory FI Where FI.SourceCountry = I.SourceCountry and FI.Data_Date = ( Select MAX(Data_date) from FactInventory FII Where FII.SourceCountry = I.SourceCountry) ) AS SumVolume
       From FactInventory I

I Display the output of above Query into Table(Direct output from Table View)

 
 



2. Then I create FilteredFactInventory(using NEW Table) with the following Formula

FilteredInventory =

VAR SelectedCountry = SELECTEDVALUE(vwInventorySummary[SourceCountry],"Argentina")

VAR SelectedDate = SELECTEDVALUE(vwInventorySummary[MaxDate],TODAY())

VAR Data = FILTER(FactInventory,FactInventory[Data_Date]=Date(2019,12,19) && FactInventory[SourceCountry]="UK")

VAR Data = FILTER(FactInventory,FactInventory[Data_Date]=SelectedDate && FactInventory[SourceCountry]=SelectedCountry)

RETURN

//FILTER(FactInventory,FactInventory[Data_Date]=[SelectedDate] && FactInventory[SourceCountry]=[SelectedCountry])

//FILTER(FactInventory,FactInventory[SourceCountry]=[SelectedCountry])

Data

 

 

3. Next i Create Measure "SelectedCountry"

MeasureSelectedCountry =
//VAR SelectedCountry = SELECTEDVALUE(vwInventorySummary[SourceCountry],"Argentina")
VAR SelectedCountry=IF(SELECTEDVALUE(vwInventorySummary[SourceCountry])=BLANK(),"UK",SELECTEDVALUE(vwInventorySummary[SourceCountry]))
RETURN
SelectedCountry
 
 

When I change the Selection in the Table above I do not get proper Filter #2 FilteredFact Table.

This is what i get when i change the selection

 

 
As can be seen above, irrespective of what i select in the top table my botton table, which is based on "FilteredInventory" Table from STEP #2 does not filter the data properly or alwayd displays data for UK
 
What could be wrong here ?
 
I Tried Setting up Relation between the Table using Model but i can only do that for Source Country, Date is Repeated in the above table for diiferent companies and it sets up Many-To-Many Relation, but even after setting that up it still does not work
 
Also another puzzling thing is that whenever i change the selection it fires the Query to DB, which takes around 7-10 Seconds to come back.
 

1 Reply