topic Re: Created a table as a variable in a measure. Filtering by one column doesn't change the results. in DAX Commands and Tips

Created a table as a variable in a measure. Filtering by one column doesn't change the results.

Singularity9 — Tue, 09 Jul 2024 12:34:11 GMT

I've created a measure. It's using a table, table1. In this table I have created a table test12 as a variable, like so:

var test1 = SUMMARIZE(

CALCULATETABLE(

Table1,

Table1[Value1]="A"

&&Table1[Area]<>"Area1"

&&Table1[Area]<>"Area2"

"), Table1[Area]

)
var test12 =

ADDCOLUMNS(

test1,

"lastmonth",

calculate([Last Month Total],Table1[Value2]="B"),

"prevmonth",

calculate([Previous Month Total],Table1[Value2]="B"),

"Difference",

calculate([Last Month Total],Table1[Value2]="B")-calculate([Previous Month Total],Table1[Value2]="B"),

"Percentage",

divide(

calculate([Last Month Total],Table1[Value2]="B")

calculate([Previous Month Total],Table1[Value2]="B"),

calculate([Previous Month Total],Table1[Value2]="B")))

Apologies, I know my code could use a cleanup. I'm trying to return values from the table test12, filtered by other values in test12. But filters don't seem to work at all. For example, when I ask it to return,

countrows(test12)& " "&

calculate(countrows(test12),filter(test12, [Percentage]>1000&&[prevmonth]+[lastmonth]>0))

It returns two values separated by a space, one which is the correct number of rows I'm expecting in table 12 (i.e., the total number of distinct areas) and one which is exactly the same as the first. The filters don't have any effect. The percentage is not over 1000 for any of the values in test12, I just used that as an example to confirm that the filter wasn't doing anything. Can anyone help? Thank you in advance.

Re: Created a table as a variable in a measure. Filtering by one column doesn't change the results.

foodd — Mon, 08 Jul 2024 16:44:16 GMT

Hello Singularity9 , and thank you for sharing a question with the Community. This reply is informational. Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you. Proud to be a Super User!

Re: Created a table as a variable in a measure. Filtering by one column doesn't change the results.

Anonymous — Tue, 16 Jul 2024 04:27:20 GMT

Hi,Singularity9
Has the problem been solved? If it does, share your solution and accept it as a solution that will help other community members who have the same problem as you.

If not, I would appreciate it if you could provide me with a PBIX file or sample data. When uploading a pbix file, delete sensitive data and don't sign in to your account in Power BI Desktop. It is recommended to use SharePoint or Onedrice

If you have any other questions please feel free to contact me.

Best Regards,

Leroy Lu