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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rembed8742
New Member

DAX Virtual row context?

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

Rembed8742_0-1719175224703.png


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

Rembed8742_1-1719176020176.png

 


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

Rembed8742_2-1719176171903.png

 

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

Many Thanks

3 REPLIES 3
tamerj1
Super User
Super User

Hi @Rembed8742 

Please try

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

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)

Thank you @tamerj1 

Hi, @Rembed8742 

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:

vjianpengmsft_0-1719803311654.png

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

vjianpengmsft_1-1719803339696.png

Here are the results:

vjianpengmsft_2-1719803367214.png

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.

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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