topic Re: Table with Blank Rows in DAX Commands and Tips

Table with Blank Rows

Chipsahoy1 — Tue, 28 Sep 2021 23:21:41 GMT

Hi Everyone,

Thank you in advance for your help. I'm farily new to Powerbi and had a question.

I have a table where I need to have the blank rows be filled in with the main service listed on the PO.

In the example below, PO 1 has a blank row, but I need that row to show as "hip" for modality, "total" for subtype, and "advanced hip" for the construct. PO 2's blank row should read knee, partial, and unicondylar. PO 3's blank rows should read shoulder, total, and reverse shoulder.

Is there a DAX that can fill in the blank rows based off of the PO #?

PO	Modality	Subtype	Construct
1	Hip	Revision	Revision
1	Hip	Total	Advanced Hip
1
2	Knee	Partial	Unicondylar Knee
2	Knee	Other	Upcharge
2
3	Shoulder	Total	Reverse Shoulder
3
3

Re: Table with Blank Rows

VahidDM — Tue, 28 Sep 2021 23:54:46 GMT

Hi Chipsahoy1

Can you tell us the logic behind the selected items for the blank rows? and can you share a sample result or how you want to use those data?

Appreciate your Kudos!!

Re: Table with Blank Rows

Chipsahoy1 — Wed, 29 Sep 2021 04:14:56 GMT

Hi VahidDM . Thanks for your quick response. The result I would like to get is below. The reasoning is because these are the major/main items for that PO. So for PO 1, the main item is listed on PO line #2. For PO #2, the major item is on line 1 and for PO #3, the major item is on line 1.

The major items aren't always on PO line #s 1, 2, or 3. They could be on line #10, or 5. It all depends on who entered the info. So unfortunately the major item's PO line # is random.

PO	PO Line#	Modality	Subtype	Construct
1	1	Hip	Revision	Revision
1	2	Hip	Total	Advanced Hip
1	3	Hip	Total	Advanced Hip
2	1	Knee	Partial	Unicondylar Knee
2	2	Knee	Other	Upcharge
2	3	Knee	Partial	Unicondylar Knee
3	1	Shoulder	Total	Reverse Shoulder
3	2	Shoulder	Total	Reverse Shoulder
3	3	Shoulder	Total	Reverse Shoulder

I'm open to suggestions on how this can be achieved. Would creating a separate table to only show the major components be an option (like below table)?

PO	Modality	Subtype	Construct
1	Hip	Total	Advanced Hip
2	Knee	Partial	Unicondylar
3	Shoulder	Total	Reverse Shoulder

Re: Table with Blank Rows

Anonymous — Wed, 29 Sep 2021 04:24:01 GMT

This can be achived in power query If you are ok with that. Or you want to do it with dax only?

Re: Table with Blank Rows

Samarth_18 — Wed, 29 Sep 2021 04:25:31 GMT

Hi Chipsahoy1 ,

You can follow steps to get your desired output:-

1. Go to trasform data tab, select your Modality,subtype and construct column and right click on it then select replace value option shown below:-

2. Now put one space in "value to find" and add null in "Replace with"

3. Now on the top ribbon click on "Fill" and select "Down"

Below would be the final output and now click on close and apply:-

Thank You,

Samarth

Re: Table with Blank Rows

VahidDM — Wed, 29 Sep 2021 05:51:39 GMT

Hi Chipsahoy1

How can we find which PO line is the main? do you have any table like key table to find that?

Appreciate your Kudos!!

Re: Table with Blank Rows

Chipsahoy1 — Wed, 29 Sep 2021 12:32:02 GMT

VahidDM Unfortunately not, but there are keywords that I know are not the main components. for subtype and construct. The keywords like "revision" and "other" for subtype and "revision", and "upcharge" for construct. What makes this a bit trickier is, a revision can be a main component if there no other components are listed on that PO (see below).

PO #	PO Line #	Modality	Subtype	Construct
4	1
4	2	Knee	Revision	Revision

So for the example above, I would need it to eventually show as this.

PO #	PO Line #	Modality	Subtype	Construct
4	1	Knee	Revision	Revision
4	2	Knee	Revision	Revision

Re: Table with Blank Rows

Chipsahoy1 — Wed, 29 Sep 2021 12:33:25 GMT

Anonymous I'm open to any possible solutions. Can you show me what you mean?

Re: Table with Blank Rows

Chipsahoy1 — Wed, 29 Sep 2021 12:46:54 GMT

VahidDM Samarth_18 Anonymous . The end goal is to have a separate table where only the main components are listed (see below) based on PO # and not have any of the secondary components listed.

PO #	Modality	Subtype	Construct
1	Hip	Total	Advanced Hip
2	Knee	Partial	Unicondylar Knee
3	Shoulder	Total	Reverse Shoulder
4	Knee	Revision	Revision