topic Re: Row context Filter in DAX Commands and Tips

Row context Filter

Anonymous — Wed, 27 Apr 2022 19:23:52 GMT

I need help calculating Gross Margin from Between Budget & Actual Based on my specific row on my Column. Here is my query

Gross Margin = IF(

HASONEFILTER(Finance[Line Description]),

DIVIDE([Actual Values]-[Budget Values],[Budget Values]))

Here is the Specific rows..

Baked Goods Gross Margin (31290) %

Cold Disp Beverage Gross Margin (31294) %

Cold Grab & Go Gross Margin (37969) %

Frozen Disp Beverage Gross Margin (31295) %

FS Prep on Site Gross Margin (31293)

Hot Disp Beverage Gross Margin (31296)

Hot Foods Gross Margin (37968)

Other Food Service Gross Margin (31292)

Other Retail Food Service Gross Margin (37970)

QSR Gross Margin (31297)

Baked Goods Gross Margin (31290)

Cold Disp Beverage Gross Margin (31294)

Cold Grab & Go Gross Margin (37969)

Frozen Disp Beverage Gross Margin (31295)

FS Prep on Site Gross Margin (31293)

Hot Disp Beverage Gross Margin (31296)

Hot Foods Gross Margin (37968)

Other Food Service Gross Margin (31292)

Other Retail Food Service Gross Margin (37970)

QSR Gross Margin (31297)

Roller Grill Gross Margin (31291)

Re: Row context Filter

tamerj1 — Wed, 27 Apr 2022 19:54:54 GMT

Hi Anonymous

Create 'Specific Rows' Table or import it from it from excel. Then create a measure

Filter Measure =
COUNTROWS ( FILTER ( Finance, Finance[Line Description] IN 'Specific Rows'[ Specific Rows]) )
Place this measure in the filter pane of the table visual and select "Is not blank" and apply the filter.

Re: Row context Filter

Anonymous — Wed, 27 Apr 2022 20:42:47 GMT

tamerj1 Please read my post. I want Budget/Actual. but, I want the % to be on specific row.. which i posted. They are other row but, i want % to be on specific row while other rows remain the same.

Here are the specific i wanted the % on.

Baked Goods Gross Margin (31290) %

Cold Disp Beverage Gross Margin (31294) %

Cold Grab & Go Gross Margin (37969) %

Frozen Disp Beverage Gross Margin (31295) %

FS Prep on Site Gross Margin (31293)

Hot Disp Beverage Gross Margin (31296)

Hot Foods Gross Margin (37968)

Other Food Service Gross Margin (31292)

Other Retail Food Service Gross Margin (37970)

QSR Gross Margin (31297)

Baked Goods Gross Margin (31290)

Cold Disp Beverage Gross Margin (31294)

Cold Grab & Go Gross Margin (37969)

Frozen Disp Beverage Gross Margin (31295)

FS Prep on Site Gross Margin (31293)

Hot Disp Beverage Gross Margin (31296)

Hot Foods Gross Margin (37968)

Other Food Service Gross Margin (31292)

Other Retail Food Service Gross Margin (37970)

QSR Gross Margin (31297)

Roller Grill Gross Margin (31291)

Re: Row context Filter

tamerj1 — Wed, 27 Apr 2022 21:07:11 GMT

Anonymous

Some like this

IF (

ISEMPTY (FILTER ( Finance, Finance[Line Description] IN 'Specific Rows'[ Specific Rows]) ),

"",

[measure]
)

however, you might recieve error

Re: Row context Filter

Anonymous — Wed, 27 Apr 2022 21:09:58 GMT

tamerj1 Thanks, i will try it. But, i tried something like below it won't display anything .

Re: Row context Filter

Anonymous — Wed, 27 Apr 2022 21:16:26 GMT

tamerj1 The Specific rows is part of the Line Description. Line Desc is the column name.

Re: Row context Filter

tamerj1 — Wed, 27 Apr 2022 21:18:03 GMT

Anonymous

I'm a little lost here. Do you want to show the value for the other rows as blanks or you want to entirely hide the row?

Re: Row context Filter

tamerj1 — Wed, 27 Apr 2022 21:22:07 GMT

Yes. You should have them in a separate single column table. Of course you should use the real name of this column and its table.

Re: Row context Filter

Anonymous — Wed, 27 Apr 2022 21:31:00 GMT

tamerj1 Your DAX works, But, i want the % to display only on the listed row not on other rows. right now it still display on other rows.

Re: Row context Filter

Anonymous — Wed, 27 Apr 2022 21:40:59 GMT

Yes!. I want to show the value of the other row as blank. Everything will be mapped to GM % only which i already have. Some like this.

Re: Row context Filter

tamerj1 — Thu, 28 Apr 2022 00:50:26 GMT

Hi Anonymous

then use

IF (

ISEMPTY (FILTER ( Finance, Finance[Line Description] IN 'Specific Rows'[ Specific Rows]) ),

[measure],

""
)

however, you might recieve error

Re: Row context Filter

v-xiaotang — Mon, 02 May 2022 09:19:13 GMT

Hi Anonymous

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Re: Row context Filter

tamerj1 — Mon, 02 May 2022 11:05:55 GMT

Hi v-xiaotang

this was answered in another post

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dax-CALCULATION-ISSUE/m-p/2485927#M68256

Re: Row context Filter

Anonymous — Tue, 03 May 2022 18:43:52 GMT

Yes, The issue was resolve by Tamer