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

Reply
tryingtohelp121
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:

 

CarsDate
Car AApril 1, 2023
Car BApril 1, 2023
Car CApril 1, 2023
Car AApril 2, 2023
Car CApril 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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

9 REPLIES 9
grantsamborn
Solution Sage
Solution Sage

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

 

 

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

Table A would be the same:

CarsDate
Car AApril 1, 2023
Car BApril 1, 2023
Car CApril 1, 2023
Car AApril 2, 2023
Car CApril 2, 2023

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

CarsDateCompleted
Car AApril 1, 2023Yes
Car BApril 1, 2023Yes
Car CApril 1, 2023Yes
Car AApril 2, 2023Yes
Car BApril 2, 2023No
Car CApril 2, 2023Yes

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.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

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?

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/

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

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/

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:

CarsDateCompletion
Car AApril 1, 2023Yes
Car BApril 1, 2023Yes
Car CApril 1, 2023Yes
Car AApril 2, 2023Yes
Car BApril 2, 2023No
Car CApril 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.

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
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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