Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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])
Best Regards
Rena
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
Best Regards
Rena
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_NUMBER | OPEN_DATE | STATUS_CODE | SHIP_PRIORITY | WHS_FROM | WHS_TO | No of Items | Originator |
4 | |||||||
SH94905 | 05/27/20 | PICK | Routine | AHW-LB | AHW-MX | 1 | LUZ |
MSH10016 | 05/21/20 | IN TRANSIT | Routine | AHW-MX | AHW-LB | 2 | JESUS |
MSH10018 | 05/21/20 | IN TRANSIT | Routine | AHW-MX | AHW-LB | 1 | JESUS |
MSH10021 | 05/26/20 | IN TRANSIT | Routine | AHW-MX | AHW-LB | 1 | JESUS |
SH94904 | 05/27/20 | XFER READY | Critical | AHW-LB | AHW-MX | 1 | LUZ |
SH94812 | 05/21/20 | IN TRANSIT | Routine | AHW-LB | AHW-MX | 3 | LUZ |
SH94855 | 05/22/20 | IN TRANSIT | Routine | AHW-LB | AHW-MX | 2 | LUZ |
Shipping Detail
SM_NUMBER | SHIP_PRIORITY | STATUS_CODE | USER_NAME | WHS_FROM | WHS_TO | PN | QTY |
SH94905 | Routine | PICK | LUZ | AHW-LB | AHW-MX | CDIN23-08S | 6900 |
MSH10016 | Routine | IN TRANSIT | JESUS | AHW-MX | AHW-LB | NAS1149F0332P | 1000 |
MSH10016 | Routine | IN TRANSIT | JESUS | AHW-MX | AHW-LB | NAS514P440-3 | 150 |
MSH10018 | Routine | IN TRANSIT | JESUS | AHW-MX | AHW-LB | MS35207-259 | 200 |
MSH10021 | Routine | IN TRANSIT | JESUS | AHW-MX | AHW-LB | CDIN07-3-11B | 100 |
SH94904 | Critical | XFER READY | LUZ | AHW-LB | AHW-MX | CDIN23-08S | 2500 |
SH94812 | Routine | IN TRANSIT | LUZ | AHW-LB | AHW-MX | 121-7 | 180 |
SH94812 | Routine | IN TRANSIT | LUZ | AHW-LB | AHW-MX | CDIN07-3-11B | 708 |
SH94812 | Routine | IN TRANSIT | LUZ | AHW-LB | AHW-MX | MS16624-4025 | 4000 |
SH94855 | Routine | IN TRANSIT | LUZ | AHW-LB | AHW-MX | AS21919WCG04 | 1750 |
SH94855 | Routine | IN TRANSIT | LUZ | AHW-LB | AHW-MX | CDSP1501-3 | 8000 |
PSH941 | Routine | PICK | ROSEANN_AHW | 4551302-001 | FAI | 1 | |
SH93017 | Routine | PICK | BOBBY | .585 X .193 X .058 | 1500 | ||
SH93017 | Routine | PICK | BOBBY | AD64H | 6000 | ||
SH93017 | Routine | PICK | BOBBY | AD86H | 7500 |
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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])
Best Regards
Rena
Thanks. That worked perfectly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |