Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rbustamante
Helper I
Helper I

Show all records from table1 and count the number of occurrences in table 2 with filters

Hi, 

I had table 1 

Id

Name

Finish Date

1

Name1

01/02/2020

2

Name2

01/03/2020

3

Name3

01/04/2020

and table 2 relation by id

Id

Value

CloseDate

1

4725

 

1

4726

28/01/2020

1

4800

 

1

4727

15/02/2020

3

4590

 

 

I want to show all the records of table 1 and count then number of ocurrences in table2 when CloseDate is blank or CloseDate is less than FinisDate. This is the result that I need:

Id

Count Of Value where CloseDate is blank or CloseDate<Finish Date

1

2

2

0

3

1

 

How can I do it with DAX?

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hello, @rbustamante

Does "show items with no data" work?



If not, you can try to create a separate computed table for the "ID" column.

ID table = DISTINCT(Table1[Id])

After the collision relationship with another table, you must replace the original "ID" field in the table visual with this new "Id" field.

 

Best regards
Community Support Team _ Eason

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

Hello, @rbustamante

Does "show items with no data" work?



If not, you can try to create a separate computed table for the "ID" column.

ID table = DISTINCT(Table1[Id])

After the collision relationship with another table, you must replace the original "ID" field in the table visual with this new "Id" field.

 

Best regards
Community Support Team _ Eason

amitchandak
Super User
Super User

@rbustamante , As table 1 and table2 are related. Use this measure with ID of table 1

 

measure = calculate(count(table2[ID]), filter(Table2, isblank(Table[CloseDate])))

Hi, the measure works and counts according my needs as you can see in then Result Table. But I need to show too the id number '2' named 'Name02' that have 0 ocurrences.

rbustamante_0-1604601474465.png

This is what I need (in yellow)

rbustamante_1-1604601840640.png

Is it posible?

I forgot the measure

Measure = CALCULATE(
COUNT(Table2[Id]),
FILTER(Table2,or(ISBLANK(Table2[CloseDate]),Table2[CloseDate]>RELATED(Table1[Finish Date])))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.