Forum Discussion

jdalmass's avatar
jdalmass
Regular Visitor
3 years ago
Solved

Visual filter on a table dependent on another table

Hello hello,

I would like to create a visualization that allows me to filter my data according to the column of a table "X" and that acts on my visualizations coming from a table "Y", itself dependent on table "X".

Here are my tables :

Table X, coming from an excel :

 

Received DateDone DateSite
.........
30/11/202224/07/2023OK
17/03/202327/07/2023OK
17/03/202327/07/2023CDS
15/03/202324/07/2023NOK
10/03/202324/07/2023CDS
10/03/202325/07/2023DOR
.........


Table Y = CALENDAR(DATE(2022,01,01), DATE(2024,01,01))

 

Received =
IF('Y'[Date] <= TODAY(),
CALCULATE(COUNT('X'[Received Date]),
FILTER('X','X'[Received Date]=='Y'[Date])))

Done =
IF('Y'[Date] <= TODAY(),
CALCULATE(COUNT('X'[Done Date]),
FILTER('X','X'[Done Date]=='Y'[Date])))

DateReceivedDone
.........
30/11/20221 
………
10/03/20232 
11/03/2023  
12/03/2023  
13/03/2023  
14/03/2023  
15/03/20231 
16/03/2023  
17/03/20232 
………
24/07/2023 2
25/07/2023 1
26/07/3023  
27/07/2023 3


And here are my visualizations :

I would like my site visualization filter to apply to my "Received and Done" visualization.
I have already tried adding FILTER or KEEPFILTER or SELECTEDVALUE and other things but have not succeeded.
The best I have found is to add :

FILTER('XI','X'[Site]="OK")
and change "OK" to the site I want, but it is not adapted to my needs.

I have also looked for similar cases on the forum, but have not managed to find a solution.

If anyone has a solution or an idea of what to do, it would be appreciated!

Thank you in advance !

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi jdalmass ,

     

    According to your code, I think they are calculated columns. As far as I know, calculated column couldn't be dynamic by slicer

    If you want to get dynamic result which is filtered by slicer, I suggest you to create measures.

    Done = 
IF(MAX('Y'[Date]) <= TODAY(),
CALCULATE(COUNT('X'[Done Date]),
FILTER('X','X'[Done Date]=MAX('Y'[Date]))))
    Received = 
IF(MAX('Y'[Date]) <= TODAY(),
CALCULATE(COUNT('X'[Received Date]),
FILTER('X','X'[Received Date]= MAX('Y'[Date]))))

    Result is as below.

    Best Regards,
    Rico Zhou

     

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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi jdalmass ,

     

    According to your code, I think they are calculated columns. As far as I know, calculated column couldn't be dynamic by slicer

    If you want to get dynamic result which is filtered by slicer, I suggest you to create measures.

    Done = 
IF(MAX('Y'[Date]) <= TODAY(),
CALCULATE(COUNT('X'[Done Date]),
FILTER('X','X'[Done Date]=MAX('Y'[Date]))))
    Received = 
IF(MAX('Y'[Date]) <= TODAY(),
CALCULATE(COUNT('X'[Received Date]),
FILTER('X','X'[Received Date]= MAX('Y'[Date]))))

    Result is as below.

    Best Regards,
    Rico Zhou

     

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