DAX measure required

[Anonymous]

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 up with 1 to 1 relationships to the Data table that only have 2 columns each.

All the tables have a unique [EMP ID] column value which their relationship is mappped to.

I need to count rows of Table2 [EMP ID] column only if values are NOT found in Table3  [EMP ID]

I'm looking for a total count #

I hope this makes sense, thanks in adance.

[fhill]

Based on your notes, i'm assuming your table structure looks like this with a Data Table, and 2 'Many to 1' Linked Tables.  (Key point here is there's no connection between EmpID on Table1 & Table 3.  Only 2 different connections to the Data Table.)

Take this in 2 easy steps.  1:  Identify if EmpID is present in Table3, and create a Custom Column as such on Table 1...

      Is In Table 3 = LOOKUPVALUE(Table3[Table 3 Data],Table3[EmpID],Table1[EmpID])

2:  Then you can use a simple IF statement in the CountRows calcuation....

     Count Table 2 = IF('Table1'[Is In Table 3] = BLANK(),BLANK(), CALCULATE(COUNTROWS('Data Table')))

Sample below.  If this isn't right, or there's more details you haven't shared, please post sample clensed data from your Tables, and screen shot of the Relationship Joins.

Forrest

[Anonymous]

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

[Anonymous]

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]

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]

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 · Accepted solution]

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]

Much apreciated Forrest, you sir are a god among men.

Now I have to investigate and learn why your solution works!

Have a great weekend.

[fhill]

Vac wo Plan =               

IF(    --If Statement--

     RELATED(      -- Looks at RELATED Values in Joined Tables--

                      'VAC POS'[EmpID]) <> BLANK()  -- Looking for Non Blank Rows in the EmpID (PosNO) data of the VAC POS Table,                                                                               but because this is Nested inside a REALTED function, it match PosNO to PosNO in both tables.  If there's no matching PosNO value in the REALTED table, a BLANK is returned instead of the RELATED Value.  Therefor <> BLANK() basically looks for ANY RELATED Value in the 2nd table.

        --IF TRUE (finds a non-blank *Related* Value) THEN --                    

                       , IF(    -- Second IF Statement --

                             RELATED(  -- Again, only looking for REALTED values (match PosNO to PosNO) but this time in the WPA POS Table

                                    'WPA POS'[PosNo]) = BLANK()    -- this time Looking for BLANK (Doesn't exist) values.

, "Blank VAC wo Plan"))   -- If True Again we have Non-Blank RELATIONs from the frist table (exists), AND Blank RELATIONs form teh 2nd table (doesn't exist).  Then return what I've typed in the "" marks.

** Typcailly IF statements have a 2nd , (comma) for =IF( (Question), Then (Result), Else (other Result)   ).  The 'Else' is always optional though, and not needed here. **