Forum Discussion

emrei's avatar
emrei
Helper I
3 years ago
Solved

Filter using an unrelated table

Hi everyone. I have the following two graph visuals made with calculated columns in the Date Table which I attached pics of its content. I want them to be dynamic using the two filters on the left which comes from the Project Type table. The issue I am having is that I have two column (actual completion date and target completion date) that I need to relate from Project Dates to Date. I tried using the userelationship function but since my calculated columns use filter in gives an circular dependency error. I have attached  the file here

https://drive.google.com/drive/folders/1jgBuVBAcRRBEryNg2XJXlqQ33szhAWEc?usp=sharing

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi emrei ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create measures. 

    _PartActualSchedule = 
CALCULATE(
    SUM('Project Dates'[% of Parts]),
    FILTER(
        ALLSELECTED('Project Dates'),
        'Project Dates'[Actual Completion Date] <= MAX('Date'[Date]) &&
        'Project Dates'[Actual Completion Date] <> 0
    )
)
    _PartTargetSchedule = 
CALCULATE(
    SUM('Project Dates'[% of Parts]),
    FILTER(
        ALLSELECTED('Project Dates'),
        'Project Dates'[Target Completion Date] <= MAX('Date'[Date])
    )
)
    _PriceActualSchedule = 
CALCULATE(
    SUM('Project Dates'[% of Price]),
    FILTER(
        ALLSELECTED('Project Dates'),
        'Project Dates'[Actual Completion Date] <= MAX('Date'[Date]) &&
        'Project Dates'[Actual Completion Date] <> 0
    )
)
    _PriceTargetSchedule = 
CALCULATE(
    SUM('Project Dates'[% of Price]),
    FILTER(
        ALLSELECTED('Project Dates'),
        'Project Dates'[Target Completion Date] <= MAX('Date'[Date])
    )
)

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. Thank you.

     

    Best Regards,

    Neeko Tang

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

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi emrei ,

     

    We can create measures. 

    _Part_Actual = DIVIDE([_PartActualSchedule],[_PartTargetSchedule],0)
    _Part_Target = DIVIDE([_PartTargetSchedule],[_PartTargetSchedule],0)
    _Price_Actual = DIVIDE([_PriceActualSchedule],[_PriceTargetSchedule],0)
    _Price_Target = DIVIDE([_PriceTargetSchedule],[_PriceTargetSchedule],0)

     

    Best Regards,

    Neeko Tang

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi emrei ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create measures. 

    _PartActualSchedule = 
CALCULATE(
    SUM('Project Dates'[% of Parts]),
    FILTER(
        ALLSELECTED('Project Dates'),
        'Project Dates'[Actual Completion Date] <= MAX('Date'[Date]) &&
        'Project Dates'[Actual Completion Date] <> 0
    )
)
    _PartTargetSchedule = 
CALCULATE(
    SUM('Project Dates'[% of Parts]),
    FILTER(
        ALLSELECTED('Project Dates'),
        'Project Dates'[Target Completion Date] <= MAX('Date'[Date])
    )
)
    _PriceActualSchedule = 
CALCULATE(
    SUM('Project Dates'[% of Price]),
    FILTER(
        ALLSELECTED('Project Dates'),
        'Project Dates'[Actual Completion Date] <= MAX('Date'[Date]) &&
        'Project Dates'[Actual Completion Date] <> 0
    )
)
    _PriceTargetSchedule = 
CALCULATE(
    SUM('Project Dates'[% of Price]),
    FILTER(
        ALLSELECTED('Project Dates'),
        'Project Dates'[Target Completion Date] <= MAX('Date'[Date])
    )
)

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. Thank you.

     

    Best Regards,

    Neeko Tang

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

    • emrei's avatar
      emrei
      Helper I
      3 years ago

      Anonymous Hey Neeko this is great, thank you. I guess I needed to do a measure instead of a column. Is there any way to get the values % out of 100. For example second picture on Aug 30 would have 0.1/0.3 =33% Actual and 0.3/0.3 = 100% target. Similarly the first picture would have 0.2/0.5 = 40% Actual and 0.5/0.5 = 100% target on Aug 30 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi emrei ,

         

        We can create measures. 

        _Part_Actual = DIVIDE([_PartActualSchedule],[_PartTargetSchedule],0)
        _Part_Target = DIVIDE([_PartTargetSchedule],[_PartTargetSchedule],0)
        _Price_Actual = DIVIDE([_PriceActualSchedule],[_PriceTargetSchedule],0)
        _Price_Target = DIVIDE([_PriceTargetSchedule],[_PriceTargetSchedule],0)

         

        Best Regards,

        Neeko Tang

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