The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have two tables:
- Cases (CaseID, ClientID, RegionID)
- Tasks (TaskID, refCaseID)
1 case can have many tasks. Tables are linked by CaseID
I would like to create an overview of how many cases have 1, 2, 3....n tasks and I would like to be able to filter results by columns from Cases, e.g. RegionID. Example data:
CountCases | CountTasks |
512 | 0 |
239 | 1 |
112 | 2 |
37 | 3 |
<blank> | 4 |
12 | 5 |
Example: 112 cases has exactly 2 associated tasks.
I have created this table, but I does not respond to filters/slicers fro table Cases, e.g. filtering the result table by RegionID
Solved! Go to Solution.
See if this works for you. For this exercise, I'm using these tables:
And have the model set up like this:
now calculate the number of tasks (DISTINCTCOUNT in this example) for each Case ID:
Count of tasks = DISTINCTCOUNT(TaskTable[Task]) +0
Next generate a new table using the "New Table" option under "Modeling" in the ribbon using the DAX:
Summarized CaseID tasks = SUMMARIZE(CaseIDTable; CaseIDTable[CaseID];
"Numb of tasks" ; [Count of tasks])
Go to the modeling pane and set up a relationship between your CaseIDTable and the new table you've just created to look like this:
Next create a measure to count the rows in the new table:
Count Case IDs by Numb of tasks = COUNTROWS('Summarized CaseID tasks')
And now you can create a table visual using the "Numb of tasks" column from the new table and the last measure you've created:
add your slicers from the CaseIdTable and you get this:
Proud to be a Super User!
Paul on Linkedin.
See if this works for you. For this exercise, I'm using these tables:
And have the model set up like this:
now calculate the number of tasks (DISTINCTCOUNT in this example) for each Case ID:
Count of tasks = DISTINCTCOUNT(TaskTable[Task]) +0
Next generate a new table using the "New Table" option under "Modeling" in the ribbon using the DAX:
Summarized CaseID tasks = SUMMARIZE(CaseIDTable; CaseIDTable[CaseID];
"Numb of tasks" ; [Count of tasks])
Go to the modeling pane and set up a relationship between your CaseIDTable and the new table you've just created to look like this:
Next create a measure to count the rows in the new table:
Count Case IDs by Numb of tasks = COUNTROWS('Summarized CaseID tasks')
And now you can create a table visual using the "Numb of tasks" column from the new table and the last measure you've created:
add your slicers from the CaseIdTable and you get this:
Proud to be a Super User!
Paul on Linkedin.
Thanks a lot! Worked perfectly.
I would have preferred to not have (yet) another physical table as my model already is far too crowded, even though my own suggestion included this.
I appreciate the effort and meticulousness you put into it. Great.
Rgds
Nick
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
78 | |
69 | |
52 | |
50 |
User | Count |
---|---|
120 | |
120 | |
76 | |
62 | |
61 |