[DAX] filter and lookup to other table to cnt records

[dreamcatcher90]

hello,

I have two tables with common identifiers (many-to-many). I want to count records from one table (with filter on specific status) based on other table, which is filtering by slicer. Report is made in Power BI as DirectQuery model. I will be grateful for any suggestions.. thanks!

[PattemManohar]

@dreamcatcher90 It will be great, if you can post some sample data (that can be copied) and expected output.

[dreamcatcher90]

<please delete this post>

[dreamcatcher90]

updated scenario:

I have two tables. First table contains three dates (created, submitted, approved) which are use to filter by slicer. Based on this columns there were created at the top of the report three cards with counts (number of records with selected date). There is also ID column. Second table contains the same ID column and specific status (successfully, pending, failed) I struggle with below cases:

1) My expected results is to count on bottom cards only IDs that were count as approved and separated this number by specific statuses

2) How to implement many-to-many relationship is such a case?

Here you can find report with test data: https://ufile.io/j7hms

I will be grateful for any help and suggestions.. thanks!

[v-juanli-msft · Accepted solution]

Hi @dreamcatcher90

"My expected results is to count on bottom cards only IDs that were count as approved and separated this number by specific statuses"

For the same ID in Table1 and Table2, when Table2[date] matchs with Table1[Approved Date], the IDs associated with the [Approved Date] should be count. This count should be total for each status and it isn't reflected by slicer.

If my understanding is right, i test with the following steps: 

1.Connect Table1 and Table2 based on [ID] column, 

2.edit queries->

Merge columns in Table1 and Table2 in the same form

Merge queries

Expand columns

Close&&apply

3.create measures

Successfully = CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),[Table2.Status]="Successfully"))

Panding = CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),[Table2.Status]="Panding"))

Failed = CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),[Table2.Status]="Failed"))

Best Regards

Maggie