Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

create static measure

Hi,

 

I am looking for creating a static measure with values 'Yes' and 'No'.

And based on the selection of the Yes or No, I want to out a case statement in another measure.

Following is what is to be achieved:

If SelectedValue = 'Yes', 

Measure1 =Sum( 'Table1'[countNumber] )

if SelectedValue = 'No', 

Measure1 =Sum( 'Table1'[countNumberExcluded] )

else 

Measure1 =Sum( 'Table1'[countNumber] )

 

Nothing can be done on the report view.

Please guide me achieving this.

Thanks in advance.

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Anonymous , Create a table using enter Data and Have two Values Yes And No.

 

Keep it independent, means do not join with others

 

Then try a measure like

If(SelectedValue(Table[column]) ="Yes" ,Sum( 'Table1'[countNumber] ),Sum( 'Table1'[countNumberExcluded] ) )

 

Also refer:https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern

 

 

View solution in original post

Greg_Deckler
Super User
Super User

@Anonymous A little hard to follow but in general for this you want to created a completely disconnected table with your values. So create a table using Enter Data and just have two rows "Yes" and "No" in Column1. 

Greg_Deckler_1-1600066449036.png

 

Greg_Deckler_0-1600066401947.png

Make sure that there are no relationships to this table.

 

Now, create a measure like this:

SelectedValue = SELECTEDVALUE('Table'[Column1)

Finally, create this measure:

Measure1 = 
  SWITCH(TRUE(),
    [SelectedValue] = "Yes",SUM( 'Table1'[countNumber] ),
    [SelectedValue] = "No", SUM( 'Table1'[countNumberExcluded] ),
    SUM( 'Table1'[countNumber] )
  )

 

 The technique is called the disconnected table trick and you can learn more about it here: In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@Anonymous A little hard to follow but in general for this you want to created a completely disconnected table with your values. So create a table using Enter Data and just have two rows "Yes" and "No" in Column1. 

Greg_Deckler_1-1600066449036.png

 

Greg_Deckler_0-1600066401947.png

Make sure that there are no relationships to this table.

 

Now, create a measure like this:

SelectedValue = SELECTEDVALUE('Table'[Column1)

Finally, create this measure:

Measure1 = 
  SWITCH(TRUE(),
    [SelectedValue] = "Yes",SUM( 'Table1'[countNumber] ),
    [SelectedValue] = "No", SUM( 'Table1'[countNumberExcluded] ),
    SUM( 'Table1'[countNumber] )
  )

 

 The technique is called the disconnected table trick and you can learn more about it here: In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks!

 

Any idea how do I make user just to select one value, either Yes or No.

amitchandak
Super User
Super User

@Anonymous , Create a table using enter Data and Have two Values Yes And No.

 

Keep it independent, means do not join with others

 

Then try a measure like

If(SelectedValue(Table[column]) ="Yes" ,Sum( 'Table1'[countNumber] ),Sum( 'Table1'[countNumberExcluded] ) )

 

Also refer:https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.