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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count if match on related table

This is my first post as I am new to Power BI....so be gentle!

 

I have a Power BI "dashboard" table with shipments with two queries.  The first query returns the open header records identified by a unique shipment ID.  The second query returns shipping detail records for items on each on each shipment.

 

On the summary dashboard I have related the two tables using the shipment ID.  I added a measure the second query that counts the number of detail records.  This field "No. of items" shows on the summary dashboard.  The measure DAX formula is: No of Items = COUNTROWS(RELATEDTABLE(AHW_BI_IN_TRANSIT)).

 

The summary dashboard has an extra blank row with a count of shipping detail records that do not have a shipping ID on the summary dashboard.  

 

I've tried DISTINCTCOUNT but that not does return the expected result.

 

Any help you provide is appreciated.

2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

FYI that there is also a DISTINCTCOUNTNOBLANK( ) function you could use in its place.

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Hi @Anonymous ,

Please try to update the formula of measure "No of Items" as below:

No of Items = 
IF (
    NOT ( ISBLANK ( MAX ( 'Shipping Summary'[SM_NUMBER] ) ) )
        && TRIM ( MAX ( 'Shipping Summary'[SM_NUMBER] ) ) <> "",
    COUNTROWS ( RELATEDTABLE ( 'Shipping Detail' ) )
)

And if the total value is not correct, you can create another new measure:

Measure = SUMX(VALUES('Shipping Summary'[SM_NUMBER]),[No of Items])

Remove blank.JPG

Best Regards

Rena

 

 

 

 

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
mahoneypat
Employee
Employee

FYI that there is also a DISTINCTCOUNTNOBLANK( ) function you could use in its place.

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Distinctcountnoblank returns a "1" for all rows in the summary table (including the blank row).  I need it return the number of detail rows in the shipping detail query that match the shipping ID in the summary table.

I think I understand your scenario better.  You shouldn't need to use RELATEDTABLE in your measure.  Just a COUNTROWS(detailtable) should do it.

 

For the blank row, as you said, that means there are shipment IDs in the detail table that don't exist in the shipment table.  You could do some more in query to make sure all rows have a match (eliminate the blank row), or you can filter out the blank row from the table using the Filter pane on the right (Filters on this visual).

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Yes, Countrows gave the same result.  I don't think I can use the filter pane because the query is refreshed "behind the scenes" and the user would have to reselect the filter each time.

 

To transform the data using the query editor sounds complicated.  Isn't there a way to "Count If shipment # is on the summary table"?

Hi @Anonymous ,

Please try to disable the feature "Show items with no data", and check if the rows with blank shipmentid still display on the visual. If it still exist, please provide some sample data of your model. Later we will check and provide the suitable method for your scenario. Thank you.

remove blank row.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

This option did not filter out the blank row.  I'm not sure how to attached an Excel file so I have created 2 tables below, the first with the summary dashboard and the second with a sample of the detail records.   But there is at least one detail record for every summary records.  There are 4 detail records that do not match the SM-Number in the summary table.  The first row in the summary table is the one I am trying to suppress.

 

Thanks for all your help.

 

Shipping Summary

SM_NUMBEROPEN_DATESTATUS_CODESHIP_PRIORITYWHS_FROMWHS_TONo of ItemsOriginator
      4 
SH9490505/27/20PICKRoutineAHW-LBAHW-MX1LUZ
MSH1001605/21/20IN TRANSITRoutineAHW-MXAHW-LB2JESUS
MSH1001805/21/20IN TRANSITRoutineAHW-MXAHW-LB1JESUS
MSH1002105/26/20IN TRANSITRoutineAHW-MXAHW-LB1JESUS
SH9490405/27/20XFER READYCriticalAHW-LBAHW-MX1LUZ
SH9481205/21/20IN TRANSITRoutineAHW-LBAHW-MX3LUZ
SH9485505/22/20IN TRANSITRoutineAHW-LBAHW-MX2LUZ

 

Shipping Detail

SM_NUMBERSHIP_PRIORITYSTATUS_CODEUSER_NAMEWHS_FROMWHS_TOPNQTY
SH94905RoutinePICKLUZAHW-LBAHW-MXCDIN23-08S6900
MSH10016RoutineIN TRANSITJESUSAHW-MXAHW-LBNAS1149F0332P1000
MSH10016RoutineIN TRANSITJESUSAHW-MXAHW-LBNAS514P440-3150
MSH10018RoutineIN TRANSITJESUSAHW-MXAHW-LBMS35207-259200
MSH10021RoutineIN TRANSITJESUSAHW-MXAHW-LBCDIN07-3-11B100
SH94904CriticalXFER READYLUZAHW-LBAHW-MXCDIN23-08S2500
SH94812RoutineIN TRANSITLUZAHW-LBAHW-MX121-7180
SH94812RoutineIN TRANSITLUZAHW-LBAHW-MXCDIN07-3-11B708
SH94812RoutineIN TRANSITLUZAHW-LBAHW-MXMS16624-40254000
SH94855RoutineIN TRANSITLUZAHW-LBAHW-MXAS21919WCG041750
SH94855RoutineIN TRANSITLUZAHW-LBAHW-MXCDSP1501-38000
PSH941 RoutinePICKROSEANN_AHW 4551302-001FAI1
SH93017RoutinePICKBOBBY  .585 X .193 X .0581500
SH93017RoutinePICKBOBBY  AD64H6000
SH93017RoutinePICKBOBBY  AD86H7500

 

 

I don't have your model to try this to confirm, but you could try something like this

 

NewMeasure = SUMX(DISTINCT(Shipments[ID]), Calculate(Countrows(DetailsTable))

 

DISTINCT does not include the blank row.

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

This DAX expression gave the exact same result as countrows(detailed-table), i.e.  it counted the non-matching detail records on the detail shipping table and put it on the summary shipping dashboard table.  See my post below with the shipping summary and detail records.

Hi @Anonymous ,

Please try to update the formula of measure "No of Items" as below:

No of Items = 
IF (
    NOT ( ISBLANK ( MAX ( 'Shipping Summary'[SM_NUMBER] ) ) )
        && TRIM ( MAX ( 'Shipping Summary'[SM_NUMBER] ) ) <> "",
    COUNTROWS ( RELATEDTABLE ( 'Shipping Detail' ) )
)

And if the total value is not correct, you can create another new measure:

Measure = SUMX(VALUES('Shipping Summary'[SM_NUMBER]),[No of Items])

Remove blank.JPG

Best Regards

Rena

 

 

 

 

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks.  That worked perfectly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.