topic Re: URGENT!! Dax query giving blank in DAX Commands and Tips

URGENT!! Dax query giving blank

Anonymous — Tue, 16 Apr 2024 02:20:01 GMT

Hello, Power Bi community,

I need help with this Dax query below: here is the sample pbix as well: https://drive.google.com/file/d/1AC0jrk_w7ik0mX34jKGTzE7cI49ySkNI/view?usp=sharing

I'm getting blank when I run this code, can you help me I can get the correct data:

The expected output for this is 0.3%.

Latest 4 Weeks %Contribution (Red) = CALCULATE( [Latest 4 Weeks %Contribution], FILTER( 'Data by Channels', 'Data by Channels'[L4W PP %Cont Color] = "Red" && 'Data by Channels'[State] <> "Total US" && 'Data by Channels'[State] <> "Core 7 States" ) )

the dependent queries are below:
Latest 4 Weeks %Contribution:

Latest 4 Weeks %Contribution = DIVIDE( SUM('Data by Channels'[Latest 4 Weeks Ending]), CALCULATE(SUM('Data by Channels'[Latest 4 Weeks Ending]), 'Data by Channels'[State] = "Total US") )

L4W PP %Cont Color:

L4W PP %Cont Color = SWITCH ( TRUE(), Max('Data by Channels'[Measures]) = "Value Share" || Max('Data by Channels'[Measures]) = "Volume Share (UC)" || Max('Data by Channels'[Measures]) = "Trial (% HH Buying, 1x)" || Max('Data by Channels'[Measures]) = "Average Weekly ACV Distribution" || Max('Data by Channels'[Measures]) = "Volume Segment" || Max('Data by Channels'[Measures]) = "Dollar Segment" || Max('Data by Channels'[Measures]) = "Repeaters (% HH Buying, 2x)" || Max('Data by Channels'[Measures]) = "Household Penetration" || CONTAINSSTRING(Max('Data by Channels'[Measures]), "Average Weekly CWD Distribution") || CONTAINSSTRING(Max('Data by Channels'[Measures]), "Volume Sales (UC) by Merch"), IF( SUM('Data by Channels'[Latest 4 Weeks Ending]) - SUM('Data by Channels'[Previous Period 4 weeks]) < 0, "Red", IF( SUM('Data by Channels'[Previous Period 4 weeks]) = 0, "Grey", "Green" ) ), TRUE(), IF( (SUM('Data by Channels'[Latest 4 Weeks Ending])/SUM('Data by Channels'[Previous Period 4 weeks])-1) < 0, "Red", IF( SUM('Data by Channels'[Previous Period 4 weeks]) = 0, "Grey", "Green" ) ) )

Please take a look and let me know if you need anything else.
Look forward to your replies.

@MFelix , @amitchandak , @Ashish_Mathur

Re: URGENT!! Dax query giving blank

Greg_Deckler — Mon, 15 Apr 2024 15:40:31 GMT

Anonymous Sample data would be of tremendous help here. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: URGENT!! Dax query giving blank

Anonymous — Tue, 16 Apr 2024 02:21:20 GMT

Hello Greg_Deckler
Thank you for the suggestion, I've attached the sample pbix and the expected output to the original post. Please take a look at this. and let me know.

Re: URGENT!! Dax query giving blank

tamerj1 — Tue, 16 Apr 2024 07:38:43 GMT

Hi Anonymous
Following a proposed solution

Latest 4 Weeks %Contribution (Red) = SUMX ( FILTER( VALUES ( 'Data by Channels'[State] ), NOT 'Data by Channels'[State] IN { "Total US", "Core 7 States" } && [L4W PP %Cont Color] = "Red" ), [Latest 4 Weeks %Contribution] )

Re: URGENT!! Dax query giving blank

Anonymous — Tue, 16 Apr 2024 07:49:11 GMT

Thanks a lot! tamerj1 for the solution.,