topic DAX Virtual row context? in DAX Commands and Tips

DAX Virtual row context?

Anonymous — Sun, 23 Jun 2024 20:58:52 GMT

Hi Guys,

I Have calulations around search volume that works well when I have rows in the context
i.e To Calulate Search Volume of the Label I Divide search volume by Search Volume (all keyword/brands/subcategories)
see below working fine

DAX:

Code to calulate search volume label:

Search Volume (Labels) = CALCULATE(

SUM( 'Search Volume'[Search Volume] ) ,

ALL(Keyword[Subcategory] ),

ALL('Product'[Brand] ),

ALL('Keyword'[keyword] )

)

Keyword level calcs

I was wondering if there is a way to retain the same values when I remove labels from the visual? - for example always pads should return 32,995 (word always has one label value)
after labels are removed I have wrong totals

I was trying summarize/addcolumns/values etc but never was able to get to the correct number, any ideas?

Many Thanks

Re: DAX Virtual row context?

tamerj1 — Mon, 24 Jun 2024 04:53:11 GMT

Hi Anonymous

Please try

Search Volume (Labels) 2 = SUMX ( VALUES ( 'Table'[Label] ), [Search Volume (Labels)] )

Re: DAX Virtual row context?

Anonymous — Mon, 24 Jun 2024 06:10:00 GMT

Not working, sumx + values creates virtual Table on the list of values and then sum all

Search Volume (Labels) values, this returns total,

I Want to simulate below
keyword, label ,search volume
kw 1, branded, 100
kw 2, branded, 50

kw 3, non branded, 100

New Measure shoud return 200 for Branded kw and 50 for non branded, (when Label is not part of the visual)

Re: DAX Virtual row context?

Anonymous — Mon, 01 Jul 2024 03:10:48 GMT

Thank you tamerj1

Hi, Anonymous

Maybe you can try using the summarize function to achieve something similar. First of all, you need to implement your summation in the summary, such as the sum of labels:

Since there is no label in your visual, we need to use your existing label to get this value, such as keyword:

Here are the results:

In the analog data you provided, branded is 150.How did you calculate that it was 200? The above is how to keep the current calculation in the visual if there is no corresponding label. You can adjust it appropriately to suit your actual situation.

Best Regards

Jianpeng Li

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