Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX measure required

Good morning Magnificent Humans,   I have been trying to crack this nut for the past couple days with no luck with my limited experience.   I have 3 tables: 1 Data (multiple columns) and 2 Look u...
Need Help
  • fhill's avatar
    fhill
    6 years ago

    Great, since 'ALL POS' has a 1-1 relationship with both tables, we can build a single 'New Column' on this table to acomplish what you need....  

     

    Vac wo Plan = IF(RELATED('VAC POS'[EmpID]) <> BLANK(), IF(RELATED('WPA POS'[PosNo]) = BLANK(), "Blank VAC wo Plan"))
     
    You can either change the final text to 1, and COUNT the results, or leave it text and 
    Count = COUNTA('All POS'[Vac wo Plan]) the new coulmn as a measure to get your result.
     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Can you show us a screenshot of the model?
Or the PBIX itself if possible.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Here is the data model however I had changed the POS NO for EMP ID in my example as from experience its easyier to understand.

 

 

So if i understand from your comments it would be best if we started from the Data Table (ALL Pos) and count only Pos No values found on Lookup Table (VAC) but not found on Lookup Table (WPA). Is that correct, and if so; is it doable?

 

Thanks in advance and I apologize for the wait.

  • fhill's avatar
    fhill
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    The screen shot helps, but I'm a bit lost again on what you are trying to do.  Now that I can see your data columns, can you ask again...   (If Present <Here>, but NOT <here>, Count <here>?)

    FOrrest

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      certainly Forrest,

       

      Pos No (Position Number) is a unique identifier of a box (position) in an organizational chart, the VAC table is the list of those Pos No's that are vacant; and the WPA Table is a list of Pon No's that have staffing plans.

      I am trying to get a count of (Total # on a Card visual) positions that are vacant but do not have a staffing plan.

       

      Calculate('ALL Pos' [Pos No], only if found on 'VAC' [Pos No] but not found on 'WPA' [Pos No].

       

      • fhill's avatar
        fhill
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        Great, since 'ALL POS' has a 1-1 relationship with both tables, we can build a single 'New Column' on this table to acomplish what you need....  

         

        Vac wo Plan = IF(RELATED('VAC POS'[EmpID]) <> BLANK(), IF(RELATED('WPA POS'[PosNo]) = BLANK(), "Blank VAC wo Plan"))
         
        You can either change the final text to 1, and COUNT the results, or leave it text and 
        Count = COUNTA('All POS'[Vac wo Plan]) the new coulmn as a measure to get your result.