topic Re: All Referencing a measure in another measure in DAX Commands and Tips

All Referencing a measure in another measure

Anonymous — Mon, 27 Sep 2021 21:17:59 GMT

Is there a way I can use ALL or other dax formula to apply a page filter only in the first 2 columns but exclude it from the last 2 columns which are measures (QTDs)?

Thanks!

Re: All Referencing a measure in another measure

Greg_Deckler — Mon, 27 Sep 2021 21:47:10 GMT

Anonymous Theoretically but would need more information. You could use something like CALCULATE([Measure],ALL('Table')) for example. 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: All Referencing a measure in another measure

Anonymous — Mon, 27 Sep 2021 22:01:00 GMT

This is a sample of what I have

Q2 QTD =
VAR W14 = CALCULATE(SUM(Query[Revenue]),Query[Fiscal Week]="2021-W14")
VAR W15 = CALCULATE(SUM(Query[Revenue]),Query[Fiscal Week]="2021-W15")

Var Result = W14+W15
Return
IF(ISBLANK(Result),0,Result)

------------------------------------

Lets say that W14= 3 and W15 = 5. I would like to receive 8.

But I have a page filter of "Fiscal Week" to show specifics one. If I filter by WK 15, WK 16, WK 17 for example. then I will only receive in Q2 QTD the result of 5 instead of 8.

So I will like to apply the page filter "Fiscal Week" to the rest of the table but excluding it from the QTD one so I can receive in this case the 8 value no matter the filters being applied.

Please let me know if this helps or additional information is required

Thank you!!

Re: All Referencing a measure in another measure

colacan — Tue, 28 Sep 2021 11:35:18 GMT

Anonymous Hi joan,

You might think CALCULATE(SUM(Query[Revenue]),Query[Fiscal Week]="2021-W14") as
"if Query[Fiscal Week] ="2021-W14" then calculate..." expecting it will return 0 if [Fiscal Week] is not "2021-W14".

That is not ture.

Actually, CALCULATE(SUM(Query[Revenue]),Query[Fiscal Week]="2021-W14" means whatever outter filter is(from your filter page), provide the value of SUM(Query[Revenue] when [Fiscal Week]="2021-W14" which is 3 in your example. that's why you always get the same answer regardless your filter.

If you want to get QTD based on your selected week you can simply use TOTALQTD with complate date table related to your Revenue table.

However if you need to keep current way, below code might give you the value you intended.

VAR W14 = IF(SELECTEDVALUE(Query[Fiscal Week])="2021-W14", SUM(Query[Revenue]),0)

hope this helps you.

Plesae mark as solution if this resolved your issue.