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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Find which cars have not completed their daily required race.

Hello all,

Say I have a table, called Table A which everytime a car finishes a race (a car races once daily), it gets recorded in this table. Now say there are a total of 3 cars, Car A, Car B, Car C, that are supposed to race once every day. How can I find out which car did not complete its race for that day?

For Example, Table A currently has the following data:

 Cars Date Car A April 1, 2023 Car B April 1, 2023 Car C April 1, 2023 Car A April 2, 2023 Car C April 2, 2023

This shows us that Car B did not complete its race for April 2, and I want to be able to find that out, and count how many cars did not complete their required race depending on the date, as well as which car and on which date.

I have tried to create a table that has all the cars, and made a measure that tells me which Car has not completed its race and on which date, and it does so fine, but when I put that in a scorecard, it doesn't accurately show the total number. For example, it could be that over 1 month, 50 cars did not complete their race, but it would only show 1 or 2. This was the solution I had slightly modified to suit the columns I have so that I can produce that measure: (Reference: Solved: Select column values which is missing on another t... - Microsoft Power BI Community)

Non Selected Table 2 Values =
VAR SelT1 =
VALUES ( Table1[Table1 Values] )
VAR T2Values =
CALCULATETABLE (
VALUES ( Table2[Table2 Values] ),
ALL ( Table1[Table1 Values] )
)
RETURN
IF (
ISFILTERED ( Table1[Table1 Period] ),
COUNTROWS ( EXCEPT ( T2Values, SelT1 ) ),
1
)

Please help me out, thanks so much.

1 ACCEPTED SOLUTION
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
9 REPLIES 9
Solution Sage

Finding the NON-EXISTENCE of something can get interesting!

I was looking at this and you supplied data for Table A but in your measure your refer to Table1[Table1 Values] and Table2[Table2 Values].

Is there any way you can supply data that covers your example (prefereably more that a couple of rows)  as well as your expected results?

That would make it much easier to arrive at a solution.

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from 1) to 2).

Frequent Visitor

Hello Grant, thanks for your reply. In this case:

Table A would be the same:

 Cars Date Car A April 1, 2023 Car B April 1, 2023 Car C April 1, 2023 Car A April 2, 2023 Car C April 2, 2023

and then the desired result that I would need is a Table that has the following values:

 Cars Date Completed Car A April 1, 2023 Yes Car B April 1, 2023 Yes Car C April 1, 2023 Yes Car A April 2, 2023 Yes Car B April 2, 2023 No Car C April 2, 2023 Yes

And then that way I can very easily create a measure with a count of the ones that have a No value for a specific date and count how many they are or which cars and dates they are.

Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Frequent Visitor

Thank you. Quick question, because I was currently actually trying a similar solution. I created a table of all the dates from the start of the year to the end, and repeated the car values for each of those dates. How can I make it so that the amount of dates in the newly created table matches the start and end date of the dates in Table A, the data table?

Super User

You are welcome.  That is how i created my Calendar Table.  Please see the formula that i have used for creating the Calendar Table.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Frequent Visitor

Hey sorry, one last question:

Currently I have a relationship between dates that I am using from Table A and Table B. But I would like to use a Cars relationship as well from Table A to Table B, so if my slicer changes dates, it would reflect in both tables, and if a slicer changes Cars, it would reflect in both as well, but I'm not sure how to go about doing this

Super User

Hi,

Where has Table B come from now?  What is contained in that Table?.  In your original question, there was only 1 Table.  Share some data, describe the question and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Frequent Visitor

Hello, sorry about that, I need to elaborate more:

I am using a mixture of your solution and a solution I was working on for practice, and have come up now with two tables. Table A remains the same, while Table B contains values for each date, namely:

Table B:

 Cars Date Completion Car A April 1, 2023 Yes Car B April 1, 2023 Yes Car C April 1, 2023 Yes Car A April 2, 2023 Yes Car B April 2, 2023 No Car C April 2, 2023 Yes

I have created a measure that counts the number of those that have No as completion, and that is the amount of cars that missed a race. I also have a table that shows the cars that have missed races. Now, I have two slicers, Slicer A is a date range slicer, and Slicer B is a Cars slicer. I have a relationship between Table A and Table B based on the date, it is a many to many relationship. I can filter the date range slicer just fine, but now I cannot filter the Cars slicer as the Cars relationship is not being used as only 1 relastionship can be active at a time. I have other fields such as engine's malfunctioned, windshields broken, etc in Table A that I need to show, so I need the slicer to use the Cars column in Table A so it filters accordingly, but now that would mean that the Cars slicer does not filter the Missed Races scorecard or table as there is no active Cars relationship between Table A and Table B, only the date gets filtered as that is the active relationship. How can I make it so that I can filter not only by the date range slicer, but by the Cars slicer as well for both of the Tables?

Thanks.

Super User

Hi,

I still do not understand.  Table B (the one that you see in my posts's screesnot) is not a calculated table - it is the result of a measure.  So how did you even create a relationship between Table A and B in the first place.

I just do not understand your question, objective and desired result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors