topic Re: Measure for One Table Being Used for All Items in DAX Commands and Tips

Measure for One Table Being Used for All Items

Connell — Tue, 26 Apr 2022 13:42:01 GMT

Hi,

So I have a structure that looks like

DataSet1

A	10
B	12

connected via first column, DataSet 2

A	A-1	25
A	A-2	65
B	B-1	20
B	B-2	15

And once again DataSet3 connected to 2 via column 1

A-1	A-1-1	global
A-1	A-1-2	universal
A-1	A-1-3	global
A-2	A-2-1	local
A-2	A-2-2	universal

And I have this DAX measure NotGlobal:

Not Global =

VAR notglobal = CALCULATE(

COUNT('DataSet3'[Column3]) +0,

Return notglobal

I need to create a table with the data in DataSet2 and DataSet1, but when I use my NotGlobal measure, it will assign 0 to things outside of DataSet3, but I only want it to assign 0 to things in DataSet3 even when I pull from the other 2. I need something like

variable1	data1	variable2	data2	NotGlobal
A	10	A-1	25	2
A	10	A-2	65	0
B	12	B-1	20
B	12	B-2	15

But instead it is giving me

variable1	data1	variable2	data2	NotGlobal
A	10	A-1	25	2
A	10	A-2	65	0
B	12	B-1	20	0
B	12	B-2	15	0

Even though the B data doesnt exist in DataSet3

Re: Measure for One Table Being Used for All Items

amitchandak — Tue, 26 Apr 2022 14:43:20 GMT

Connell , That is because of +0, plus 0 will add 0 for all row having blank

remove +0 and try this

Re: Measure for One Table Being Used for All Items

Connell — Tue, 26 Apr 2022 15:01:42 GMT

Thanks amitchandak,
I want to actually indicate 0 for the items that are just in DataSet3. If I remove the 0, then they will be blank, along with other items not in DataSet3, so I won't be able to distinguish them. So if I do show items with no data, that doesn't solve what I am trying to do. I hope that adds some clarity.

Re: Measure for One Table Being Used for All Items

amitchandak — Tue, 26 Apr 2022 15:12:25 GMT

Connell

What we need to do is push the filter to restrict 0 only for dataset3

Not Global =
VAR notglobal = CALCULATE(
COUNT('DataSet3'[Column3]) +0,
Return calculate( notglobal, filter('DataSet3','DataSet3'[Col1] = max('DataSet2'[variable2])))

Re: Measure for One Table Being Used for All Items

Connell — Tue, 26 Apr 2022 15:32:38 GMT

I tried to make the adjustments and it was unsuccessful, everything is still showing 0. Perhaps because of the relationship it just picks up the calculation regardless?

Re: Measure for One Table Being Used for All Items

tamerj1 — Wed, 27 Apr 2022 13:17:22 GMT

Hi Connell

you may try

Global = SUMX ( 'DataSet3', IF ( 'DataSet3'[Column3] = "global", 1, 0 ) )

Re: Measure for One Table Being Used for All Items

Connell — Wed, 27 Apr 2022 18:36:58 GMT

my problem is that all of the items not in that data set are taking on the calculation when a table is created using variables of another dataset, some of which are shared and some of which are not. The ones that are not shared, I thought, shouldn't take on the calculation, but they do and recieve a 0

Re: Measure for One Table Being Used for All Items

tamerj1 — Wed, 27 Apr 2022 18:45:20 GMT

Connell

Ok. Would you please try

Global =
IF (
NOT ISEMPTY ( 'DataSet3' ),
SUMX ( 'DataSet3', IF ( 'DataSet3'[Column3] = "global", 1, 0 ) )
)

Re: Measure for One Table Being Used for All Items

v-chenwuz-msft — Fri, 29 Apr 2022 08:39:31 GMT

Hi Connell ,

Please try this expression:

Measure = IF ( ISBLANK ( COUNT ( 'DataSet3'[Column3] ) ), BLANK (), COUNT ( 'DataSet3'[Column3] ) + 0 )

Best Regards

Community Support Team _ chenwu zhu

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

Re: Measure for One Table Being Used for All Items

Connell — Fri, 29 Apr 2022 11:47:54 GMT

I was able to adapt this to my powerBI and it works. Thank you very much for your help.