Forum Discussion

WinterGarden's avatar
WinterGarden
Resolver I
2 years ago
Solved

Measure for filtering a table based on filtered values from another table

Hi, In the below screenshot,   "Delivery Lead" is the lead filter, which is from a master table called Engagement tracker. "Project Name" column in the table visual is from a table called...
Need Help
Show and Tell
  • Irwan's avatar
    Irwan
    2 years ago

    hello WinterGarden 

     

    sorry, my bad. i didnt read your post thoroughly, with "Open" filter it does become 2.

     

    so, here is the adjustment.

    create a new measure with following DAX

    NC Count = 
    var _Project = VALUES('Project'[Project Name])
    Return
    CALCULATE(
        COUNTROWS('IDR'),
        TREATAS(_Project,'IDR'[Project Name]),
        FILTER(
            'IDR',
            'IDR'[Category]="NC"&&
            'IDR'[Finding Status]="Open"
        )
    )
     
    as your requested, 
    1. no relationship between Project and IDR.

     

    2.1. Lead slicer from Engagement tbl

    2.2 Project slicer from Project tbl

     

    i assumed there is still a relationship between engagement and project since you didnt mentioned it.
    or is this saying about no relationship between those three tabels?

     

    Hope this will help.

    Thank you.

Irwan's avatar
Irwan
Super User
2 years ago

hello WinterGarden 

 

is this what you are looking for?

 

create a new measure with following DAX

NC Count = CALCULATE(COUNTROWS('IDR'),'IDR'[Category]="NC")
 
also the count should be 3, not 2 as you mentioned above.
As you said, Lead "A" will filter "ENG 1" and "ENG 2".
"ENG 1" has 2 NC and "ENG 2" has 1 NC.
So total NC for Lead "A" is 3.
 
Hope this will help you.
Thank you.
WinterGarden's avatar
WinterGarden
Resolver I
2 years ago

Hi Irwan ,

Lead filter is from Engagement tracker table
Project Name in that table visual is from project Dimension table
i need to calculate the count of project name where category = NC and Finding Status = Open from IDR table.
Also there is no relationship between IDR table and project Dimension table

  • Irwan's avatar
    Irwan
    Super User
    2 years ago

    hello WinterGarden 

     

    sorry, my bad. i didnt read your post thoroughly, with "Open" filter it does become 2.

     

    so, here is the adjustment.

    create a new measure with following DAX

    NC Count = 
    var _Project = VALUES('Project'[Project Name])
    Return
    CALCULATE(
        COUNTROWS('IDR'),
        TREATAS(_Project,'IDR'[Project Name]),
        FILTER(
            'IDR',
            'IDR'[Category]="NC"&&
            'IDR'[Finding Status]="Open"
        )
    )
     
    as your requested, 
    1. no relationship between Project and IDR.

     

    2.1. Lead slicer from Engagement tbl

    2.2 Project slicer from Project tbl

     

    i assumed there is still a relationship between engagement and project since you didnt mentioned it.
    or is this saying about no relationship between those three tabels?

     

    Hope this will help.

    Thank you.

    • WinterGarden's avatar
      WinterGarden
      Resolver I
      2 years ago

      Hi Irwan ,
      yes,These are the relationship that are created between those 3 tables:
      1) Engagement tracker(Project Name) ---- project Dimension(Project Name)
      2) IDR(Project Name) --- Engagement tracker(Project Name)

      but there is no relationship between project Dimension & IDR

      • Irwan's avatar
        Irwan
        Super User
        2 years ago

        hello WinterGarden 

         

        i am not sure i am following your description? dont understand --- mean..

         

        but is the DAX working for you?

        Thank you.