Set a value for value not find in another table with period filter

[kdubray]

Hello,

I'm a Power BI beginner and here is a problem I have and that I'm trying to solve for hours...

I have a tableA that contains the following values:

ID; PERIOD; CATEGORY

1; P1; A

2; P1; B

2; P2; A

3; P2; B

In this table, for a same ID, the CATEGORY can be different for 2 different periods.

And also, an ID can not exist for a period.

I have a tableB that contains all the ID and related informations

ID; NAME; ...

1; AAA; ...

2; BBB; ...

3; CCC; ...

tableA and tableB is linked with ID.

I want to get for a specific PERIOD selection with a slicer the number of ID per CATEGORY but I want to create a "NA" value for CATEGORY that represents the ID in tableA who are not found in tableB for the chosen period.

Example, if "P1" is selected, the table result should be :

VALUE; CountOfID

A; 1

B; 1

NA; 1 

I tried to build a measure that checks if the ID in tableB exists in tableA for the selected period and return the CATEGORY value if yes and "NA" if not but I'm unable to get something that works.

Does someone has an idea on how to perform that without having to make a full join of the two tables before data import?

Thanks for your help.

[Cmcmahan · Accepted solution]

I think what you're missing is how slicers affect context in Power BI.

When you take a slicer and select a single period, all other visuals will display data like the source data table only has the rows that match the slicer.  So when you select P1, the version of tableA that is being used by your visual doesn't include the P2 rows.  Since the rows aren't there, you can't count them easily.

There is also the problem of trying to add a new virtual category.  Power BI does NOT handle this situation gracefully.  You need the N/A category in the data table to be able to easily display it in the table visual.

However, if you were open to having the count of N/A IDs in a seperate visual (like a card) you can create a measure like this to give you that number:

CountFilteredOutIDs =
COUNTROWS( EXCEPT( VALUES(tableB[ID]), VALUES(tableA[ID]) ) ) + 0

This relies on having the Cross filter direction of the tableB to tableA relationship be single, but it does give the correct number of IDs that have been filtered out in tableA that exist in tableB.  It will even work with multiple slicers at the same time.  I've attached a sample .pbix that demonstrates what I'm talking about.