Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
"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
@dreamcatcher90 It will be great, if you can post some sample data (that can be copied) and expected output.
Proud to be a PBI Community Champion
<please delete this post>
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!
"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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |