Measure for One Table Being Used for All Items

[Connell]

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

[v-chenwuz-msft]

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.

[tamerj1]

Hi @Connell 

you may try

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

[Connell]

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

[tamerj1 · Accepted solution]

@Connell 

Ok. Would you please try

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

[Connell]

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

[amitchandak]

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

remove +0 and try this

[Connell]

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.

[amitchandak]

@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])))

[Connell]

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?