Find which cars have not completed their daily required race.

[tryingtohelp121]

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.

[grantsamborn]

Hi @tryingtohelp121 

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).

[tryingtohelp121]

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.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[tryingtohelp121]

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?

[Ashish_Mathur]

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

[tryingtohelp121]

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

[Ashish_Mathur]

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.

[tryingtohelp121]

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.

[Ashish_Mathur]

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.