Show value filtered by a column

[Anonymous]

Hello,

I have a table  with 3 columns, The value of "National" Group is the sum of value of Region 1 and Region 2.

Object	Group	Value
x	National	2
y	National	3
x	Region 1	1
x	Region 2	1

I create a matrix and i have this:

	National	Region 1
x	2	1
y	3

I want to filter dynamically the table and show only object of Region 1 without Blank cell and have this:

	National	Region 1
x	2	1

I try to create a measure by filtering the column Group  with : Calculate( Sum(Value), Group="Region 1") but it's not working and i don't have anymore the column National in the matrix. Do you have any idea for resolving this problem please ?

Thank you

[Anonymous]

Hello,

@parry2k 
Firstly, thank you for your help for my problem i already accept your proposition as the solution but i have another question , if i want to add another column in the matrix the filter in the measure doesn't work anymore

Now I have a table  with 4 columns, The value of "National" Group is the sum of value of Region 1 and Region 2.

Object	Group	Value	Master
x	National	2	M1
y	National	3	M1
x	Region 1	1	M2
x	Region 2	1	M3

Solution:

testmeasure =

var y =CALCULATE(SUM('Table'[Value]),'Table'[Group]="Region1")
return SUM('Table'[Value])*DIVIDE(y,y))

How can i modify the dax code for having this ?

	M1	M2
	National	Region 1
x	2	1

Thank you for your help!

[parry2k · Accepted solution]

@Anonymous you cannot do that, if you are trying to do in a one measure then you have to change it:

testmeasure =

var y =CALCULATE(SUM('Table'[Value]),'Table'[Group]="Region1")
return SUM('Table'[Value])*DIVIDE(y,y))

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Thank you it's working

[parry2k]

@Anonymous try these measures:

Sum Base = SUM ( Table[Value] )

Sum Region 1 = CALCULATE ( [Sum Base], Table[Region] = "Region 1" )

Sum when Region 1 is not blank =
VAR __region1 = [Region 1]
RETURN
[Sum Base] * DIVIDE ( __region1, __region1 )

Use sum when region 1 is not blank measure in your visual.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

I try it :

testmeasure =

var x= SUM('Table'[Value])

var y =CALCULATE(x,'Table'[Group]="Region1")

return IF(y=BLANK(),BLANK(),x*DIVIDE(y,y))

What do you mean by [Region 1] and Table[Region ] ?, It's not working i have only 3 column Obejct, Group and Value

[parry2k]

@Anonymous is it a fixed requirement to show only where region 1 has value ?

[Anonymous]

Yes it's fixed requierement